Query: network_subnets_for_compute_virtual_machine_scale_set

with subnet_list as (
  select
    lower(c -> 'properties' -> 'subnet' ->> 'id') as subnet_id,
    lower(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
    s.id is not null
    and lower(s.id) = $1
)
select
  lower(s.id) as subnet_id
from
  subnet_list as l
  left join azure_subnet as s on lower(s.id) = lower(l.subnet_id)
where
  s.id is not null;