Query: network_virtual_networks_for_compute_virtual_machine

with network_interface_id as (
  select
    id,
    jsonb_array_elements(network_interfaces)->>'id' as nic_id
  from
    azure_compute_virtual_machine
  where
    lower(id) = $1
    and subscription_id = split_part($1, '/', 3)
), subnet_id as (
    select
      s.id as id,
      s.virtual_network_name
    from
      azure_network_interface as nic,
      jsonb_array_elements(ip_configurations) as c
      left join azure_subnet as s on lower(s.id) = lower(c -> 'properties' -> 'subnet' ->> 'id')
    where
      lower(nic.id) in (select lower(nic_id) from network_interface_id)
)
select
  lower(vn.id) as virtual_network_id
from
  azure_virtual_network as vn,
  jsonb_array_elements(subnets) as s
  left join subnet_id as sub on lower(sub.id) = lower(s ->> 'id')
where
  lower(s ->> 'id') in (select lower(id) from subnet_id);