Query: network_virtual_networks_for_compute_virtual_machine_scale_set

with subnet_list as (
  select
    c -> 'properties' -> 'subnet' ->> 'id' as subnet_id,
    s.id as scale_set_id,
    n ->> 'name' as nic_name
  from
    azure_compute_virtual_machine_scale_set as s,
    jsonb_array_elements(
      virtual_machine_network_profile -> 'networkInterfaceConfigurations'
    ) as n,
    jsonb_array_elements(n -> 'properties' -> 'ipConfigurations') as c
  where
    lower(s.id) = $1
)
select
  lower(vn.id) as 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
  )