Query: network_public_ips_for_compute_virtual_machine

with network_interfaces as (
  select
    vm.id as vm_id,
    nic.id as nic_id,
    nic.ip_configurations as ip_configuration
  from
    azure_compute_virtual_machine as vm,
    jsonb_array_elements(network_interfaces) as n
    left join azure_network_interface as nic on lower(nic.id) = lower(n ->> 'id')
  where
    lower(vm.id) = $1
    and vm.subscription_id = split_part($1, '/', 3)
)
select
  lower(p.id) as public_ip_id
from
  network_interfaces as n,
  jsonb_array_elements(ip_configuration) as ip_config
  left join azure_public_ip as p on lower(p.id) = lower(ip_config -> 'properties' -> 'publicIPAddress' ->> 'id')
where
  p.id is not null;