Query: network_subnets_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)
)
select
  lower(s.id) as subnet_id
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);