Query: network_firewall_unrestricted_outbound_status

with outbound_fw as (
  select
    id
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as i
  where
    i -> 'destinations' -> 'addresses' = '["0.0.0.0/0","::/0"]'
    and i ->> 'protocol' <> 'icmp'
  group by
    id
)
select
  status,
  count(*) as "Firewalls"
from
  (
    select
      case
        when ofw.id is null then 'restricted'
        else 'unrestricted'
      end as status
    from
      digitalocean_firewall as df
      left join outbound_fw as ofw on df.id = ofw.id
  ) as outbound_status
group by
  status;