是否可以按json_agg分组并获得计数?

Question

TL;DR:是否有一种方法可以使用json_agg从连接表中获取每个不同值的计数?

我有以下格式的数据在Postgres数据库:

people

<表类> id 名称 tbody><<tr>1丹2弗雷德3鲍勃

使用您的查询，我将首先聚合engagement_types。Id-s到每个人的数组中。id然后将结果用作CTE，并将engagement_types_ids数组重塑为JSONB格式。请注意，engagement_types_ids数组可能/将有重复的元素。因此，将数组转换为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;

编辑
如果你认为这种情况足够通用，那么创建一个可重用的函数将数组重塑为上述JSON格式可能是有意义的。

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;
$$;

然后查询变得更加简单和清晰。

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

或者-首先聚合people.id, engagement_types.id，然后仅聚合people.id，然后使用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;