Is it possible to group by and get counts within a json

TL;DR: Is there a way to use json_agg to get the counts for each distinct value from a join table?

I have data in the following format in a Postgres DB:

people

id	name
1	Dan
2	Fred
3	Bob

engagements

id	title	engagement_types_id	people_id
1	meeting1	1	1
2	meeting2	2	2
3	meeting3	3	3
4	meeting4	1	1
5	meeting5	2	2
6	meeting6	3	3

engagement_types

id	title
1	a
2	b
3	c

And I'm trying to write a single query that gets the number of engagement types by people and the total number of overall engagements.

So far, I have the following query:

SELECT
  people.id,
  COUNT(*) OVER () AS "total",
  json_agg(distinct engagement_types.id)    
FROM people
INNER JOIN engagements on engagements.people_id = people.id
INNER JOIN engagement_types on engagement_types.id = engagements.engagement_types_id
GROUP BY people.id

Which I'm able to use to get the distinct types, but not the counts. I'm struggling to wrap my head around jsonb_build_object and json_agg. Is there a way to use json_agg to get the counts for each distinct engagement_types.id?

I'm trying to get the data in the following format:

people_id	total	engagements_by_type
1	2	[{"id": "1", "count": 2}, {"id": "2", "count": 1}]
2	3	[{"id": "1", "count": 1}, {"id": "2", "count": 2}]
3	4	[{"id": "1", "count": 3}, {"id": "2", "count": 3}]

CodePudding user response：

Using your query I would first aggregate engagement_types.id-s into an array per people.id then use the result as a CTE and reshape the engagement_types_ids array into your JSONB format. Please note that engagement_types_ids array may/will have repeating elements. Hence the relatively complicated conversion of the array into JSON.

with t as
(
SELECT 
 people.id,
 COUNT(*) "total",
 array_agg(engagement_types.id) engagement_types_ids
FROM people
INNER JOIN engagements on engagements.people_id = people.id
INNER JOIN engagement_types on engagement_types.id = engagements.engagement_types_id
GROUP BY people.id
)
select id, "total",
 (
   select jsonb_agg(jsonb_build_object('id', i, 'count', cnt)) 
   from 
   (
    select i, count(*) cnt from unnest(engagement_types_ids) i group by i
   ) t
 ) engagements_by_type
from t;

Edit
If you think that the case is generic enough then it may make sense to create a reusable function that reshapes an array into the above JSON format.

create or replace function arrray_element_groups(arr anyarray)
returns jsonb language sql immutable as
$$
 select jsonb_agg(jsonb_build_object('id', i, 'count', cnt)) 
 from (select i, count(*) cnt from unnest(arr) i group by i) t;
$$;

Then the query becomes much simpler and cleaner.

SELECT 
 people.id,
 COUNT(*) "total",
 arrray_element_groups(array_agg(engagement_types.id)) engagements_by_type
FROM people
INNER JOIN engagements on engagements.people_id = people.id
INNER JOIN engagement_types on engagement_types.id = engagements.engagement_types_id
GROUP BY people.id

Alternatively - aggregate first by people.id, engagement_types.id and then by people.id only and use jsonb_agg(jsonb_build_object(...))

with t as
(
SELECT 
 people.id people_id, engagement_types.id types_id, count(*) cnt 
FROM people
INNER JOIN engagements on engagements.people_id = people.id
INNER JOIN engagement_types on engagement_types.id = engagements.engagement_types_id
GROUP BY people.id, engagement_types.id
)
select people_id, sum(cnt) total, 
       jsonb_agg(jsonb_build_object('id', types_id, 'count', cnt)) engagements_by_type
from t
group by people_id;