Query: compute_virtual_machines_for_network_public_ip

with vm_network_interface as (
  select
    id,
    jsonb_array_elements(network_interfaces) ->> 'id' as n_id
  from
    azure_compute_virtual_machine
),
ni_public_ip as (
  select
    id,
    jsonb_array_elements(ip_configurations) -> 'properties' -> 'publicIPAddress' ->> 'id' as pid
  from
    azure_network_interface
)
select
  lower(v.id) as virtual_machine_id
from
  vm_network_interface as v
  left join ni_public_ip as n on lower(v.n_id) = lower(n.id)
  left join azure_public_ip as p on lower(n.pid) = lower(p.id)
where
  lower(p.id) = $1;