PostgreSQL关联多对多表后，如何输出每个cid的唯一JSON聚合列

问题场景 #

我在PostgreSQL中有一个查询，返回JSON类型的列：

SELECT PC.cid, json_object_agg(PC.pid, PC.map)
FROM process_table AS PC
WHERE PC.pid=240 OR PC.pid=63
GROUP BY PC.cid

该查询返回结果正确，每个cid对应唯一的JSON聚合结果：

cid |  json_object_agg
---------+--------------------
  625144 | { "63" : "gfff" }
  665087 | { "63" : "sdfg" }
  721472 | { "63" : "qerdd" }
  756135 | { "63" : "dasdfg" }
 2262581 | { "63" : "fgyhh" }

当我将其与另一张存在多对多关系的system_tables_2表关联后，cid不再唯一，且需要统计sid的数量，但希望每个cid只输出唯一的json_object_agg列（同一cid的该列内容一致，取任意一个即可）。关联后的查询如下：

SELECT F.cid, COUNT(SC.sid)
    FROM (
        SELECT PC.cid, json_object_agg(PC.pid, PC.map)
        FROM process_table AS PC
        WHERE PC.pid=240 OR PC.pid=63
        GROUP BY PC.cid
    ) AS F
INNER JOIN system_tables_2 as SC ON SC.cid=F.cid
GROUP BY F.cid

解决方法 #

方法1：用聚合函数包裹JSON列 #

因为同一cid对应的json_object_agg内容完全一致，使用MAX()或MIN()即可提取唯一值，同时保留分组统计逻辑：

SELECT F.cid, MAX(F.json_object_agg) AS json_data, COUNT(SC.sid)
FROM (
    SELECT PC.cid, json_object_agg(PC.pid, PC.map)
    FROM process_table AS PC
    WHERE PC.pid=240 OR PC.pid=63
    GROUP BY PC.cid
) AS F
INNER JOIN system_tables_2 as SC ON SC.cid=F.cid
GROUP BY F.cid

方法2：先统计关联表计数再关联 #

先对system_tables_2按cid分组统计sid数量，再和生成JSON的子查询关联，从根源避免重复分组导致的JSON列重复问题：

SELECT F.cid, F.json_object_agg AS json_data, SC.sid_count
FROM (
    SELECT PC.cid, json_object_agg(PC.pid, PC.map)
    FROM process_table AS PC
    WHERE PC.pid=240 OR PC.pid=63
    GROUP BY PC.cid
) AS F
INNER JOIN (
    SELECT cid, COUNT(sid) AS sid_count
    FROM system_tables_2
    GROUP BY cid
) AS SC ON SC.cid=F.cid

方法3：使用PostgreSQL专属的DISTINCT ON #

利用DISTINCT ON特性按cid去重，结合窗口函数统计sid数量：

SELECT DISTINCT ON(F.cid) F.cid, F.json_object_agg AS json_data, COUNT(SC.sid) OVER (PARTITION BY F.cid) AS sid_count
FROM (
    SELECT PC.cid, json_object_agg(PC.pid, PC.map)
    FROM process_table AS PC
    WHERE PC.pid=240 OR PC.pid=63
    GROUP BY PC.cid
) AS F
INNER JOIN system_tables_2 as SC ON SC.cid=F.cid

内容的提问来源于stack exchange，提问作者bcsta