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
),
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
  );