Query: identity_groups_with_policy

with group_with_policy as (
  select
    distinct g.name as group_name,
    g.id as group_id
  from
    oci_identity_policy as p,
    jsonb_array_elements_text(statements) as s,
    oci_identity_group as g
  where
    s ilike '%' || g.name || '%'
),group_without_policy as (
  select
    id,
    case
      when id not in (select distinct group_id from group_with_policy) then 'no policies'
      else 'with policies'
    end as has_policies
  from
    oci_identity_group
)
  select
    has_policies,
    count(*)
  from
    group_without_policy
  group by
    has_policies;