Query: identity_user_by_groups

with users_and_grp as (
  select
    oci_identity_user.name as user_name,
    oci_identity_group.name as group_name,
    user_group ->> 'groupId' as group_id
  from
    oci_identity_user,
    jsonb_array_elements(user_groups) as user_group
    inner join oci_identity_group ON (oci_identity_group.id = user_group ->> 'groupId' )
)
select
  group_name as "Group Name",
  count(user_name) as "Users"
from
  users_and_grp
group by
  group_name
order by
  group_name;