Query: identity_groups_without_users

with user_group as (
  select
    jsonb_array_elements(user_groups) ->> 'groupId' as g_id
  from
    oci_identity_user
), groups_without_users as (
    select
      id,
      case
        when id not in (select distinct g_id from user_group) then 'no users'
        else 'with users'
      end as has_users
    from
      oci_identity_group
    )
  select
    has_users,
    count(*)
  from
    groups_without_users
  group by
    has_users;