Query: network_virtual_networks_for_compute_virtual_machine_scale_set_vm

with ip_configs as (
  select
    nic.ip_configurations as ip_config,
    lower(vm.id) as vm_id,
    lower(nic.id) as nic_id
  from
    azure_compute_virtual_machine_scale_set_vm as vm
    left join azure_compute_virtual_machine_scale_set_network_interface as nic on lower(vm.id) = lower(nic.virtual_machine ->> 'id')
  where
    lower(vm.id) = $1
),
subnet_list as (
  select
    lower(c -> 'properties' -> 'subnet' ->> 'id') as subnet_id
  from
    ip_configs,
    jsonb_array_elements(ip_config) as c
)
select
  lower(vn.id) as virtual_network_id
from
  azure_virtual_network as vn,
  jsonb_array_elements(vn.subnets) as s
where
  lower(s ->> 'id') in (
    select
      lower(subnet_id)
    from
      subnet_list
  )