PostgreSQL中JSON_AGG用DISTINCT报错，求无需指定字段的去重方案

问题根源 #

你遇到的重复是因为modules和documents左连接后产生了笛卡尔积：比如一个agency下有2个模块和3个文档，连接后会生成6条记录，聚合时每个模块会被重复统计3次，每个文档被重复统计2次。而直接用DISTINCT modules.*报错，是因为PostgreSQL默认没有为自定义行类型（modules表的行类型）提供相等比较操作符。

方案1：提前子查询聚合（推荐，性能更优） #

先分别对modules和documents按agency分组聚合，再与agencies关联，从根源避免笛卡尔积：

SELECT 
  agencies.*,
  m.activeModules,
  m.draftModules,
  d.activeDocuments
FROM agencies
LEFT JOIN (
  SELECT 
    agency_id,
    JSON_AGG(modules.*) FILTER(WHERE status = 'active') AS activeModules,
    JSON_AGG(modules.*) FILTER(WHERE status = 'draft') AS draftModules
  FROM modules
  GROUP BY agency_id
) m ON agencies.id = m.agency_id
LEFT JOIN (
  SELECT 
    agency_id,
    JSON_AGG(documents.*) FILTER(WHERE status = 'active') AS activeDocuments
  FROM documents
  GROUP BY agency_id
) d ON agencies.id = d.agency_id
WHERE agencies.id = '959e5e04-e8ba-4367-b3d7-8bc5d5b5e666';

方案2：将行转成JSONB后去重 #

利用PostgreSQL对JSONB类型支持相等比较的特性，把整行转成JSONB后再用DISTINCT去重，无需逐个指定字段：

SELECT 
  agencies.*,
  JSON_AGG(DISTINCT to_jsonb(modules)) FILTER(WHERE modules.status = 'active') AS activeModules,
  JSON_AGG(DISTINCT to_jsonb(modules)) FILTER(WHERE modules.status = 'draft') AS draftModules,
  JSON_AGG(DISTINCT to_jsonb(documents)) FILTER(WHERE documents.status = 'active') AS activeDocuments
FROM agencies
LEFT OUTER JOIN modules ON agencies.id = modules.agency_id
LEFT OUTER JOIN documents ON agencies.id = documents.agency_id
WHERE agencies.id = '959e5e04-e8ba-4367-b3d7-8bc5d5b5e666'
GROUP BY agencies.id;

如果需要保持和原modules.*一致的JSON结构，也可以用row_to_json(modules)代替to_jsonb(modules)。

补充说明 #

• 方案1避免了笛卡尔积，在数据量较大时性能远优于方案2；
• 若status是固定枚举值，用=代替like更高效（like用于模糊匹配，这里不需要）。

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