Query: network_virtual_networks_for_key_vault

with subnet as (
  select
    lower(s.id) as id
  from
    azure_key_vault as v,
    jsonb_array_elements(network_acls -> 'virtualNetworkRules') as r
    left join azure_subnet as s on lower(s.id) = lower(r ->> 'id')
  where
    lower(v.id) = $1
    and v.subscription_id = split_part($1, '/', 3)
)
select
  lower(n.id) as virtual_network_id
from
  azure_virtual_network as n,
  jsonb_array_elements(subnets) as s
where
  lower(s ->> 'id') in (select id from subnet)