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;