Query: compute_instance_by_public_ip

with instance_with_access_config as (
select
  case
    when d ->> 'natIP' is not null then 'public'
    else 'private'
  end as ip_address
from
  gcp_compute_instance,
  jsonb_array_elements(network_interfaces) nic,
  jsonb_array_elements(nic -> 'accessConfigs') d
)
select
  ip_address,
  count(*)
from
  instance_with_access_config
group by
  ip_address;