Query: network_public_ips_for_network_interface

with network_interface_public_ip as (
  select
    id,
    jsonb_array_elements(ip_configurations)->'properties'->'publicIPAddress'->>'id' as pid
  from
    azure_network_interface
  where
    subscription_id = split_part($1, '/', 3)
)
select
  lower(p.id) as public_ip_id
from
  network_interface_public_ip as n
  left join azure_public_ip as p on lower(p.id) = lower(n.pid)
where
  n.pid is not null
  and lower(n.id) = $1;