如何对两次json_agg调用的结果实现扁平化聚合

原方案合理性评估 #

你的原有实现是合理的。分层聚合的写法规避了直接关联三张表后多重聚合导致的数据重复问题：如果直接左联三张表后直接聚合，会因为两级一对多的关联关系导致productOptions的名称被重复计数，你现在先按productOption聚合对应图片、再按product聚合对应选项的分层逻辑，能保证聚合结果的准确性。

扁平化图片列表实现 #

要得到一维的图片聚合列表，有两种常用的实现方案：

方案1：基于原有分层逻辑改造（适合大数据量场景） #

在原有分层聚合的基础上，对中间的二维图片数组做展开后再合并，示例语句如下：

SELECT 
  p.name, 
  o.options, 
  array_agg(image_file) AS images
FROM products p
LEFT JOIN (
    SELECT 
      "productId", 
      array_agg(name) AS options,
      unnest(array_agg(i.images)) AS option_images
    FROM "productOptions" o
    LEFT JOIN (
        SELECT 
          "productOptionId", 
          array_agg("fileName") AS images
        FROM images
        GROUP BY "productOptionId"
    ) i ON i."productOptionId" = o.id
    GROUP BY "productId"
) o ON o."productId" = p.id
LEFT JOIN unnest(o.option_images) AS image_file ON true
GROUP BY p.id, p.name, o.options;

方案2：直接关联去重聚合（适合小数据量场景） #

如果表数据量不大，可以直接关联三张表后分别去重聚合，写法更简洁：

SELECT
  p.name,
  array_agg(DISTINCT o.name) FILTER (WHERE o.name IS NOT NULL) AS options,
  array_agg(i.fileName) FILTER (WHERE i.fileName IS NOT NULL) AS images
FROM products p
LEFT JOIN "productOptions" o ON p.id = o."productId"
LEFT JOIN images i ON o.id = i."productOptionId"
GROUP BY p.id, p.name;

两种方案最终返回的结果都是一维图片数组：

name     |            options            |              images              
-------------+-------------------------------+----------------------------------
 Shampoo     | {"Hair Growth","Frizzy Hair"} | {bee.png, fancy.png, soap.png}
 Conditioner |                               | 

选型建议 #

• 表数据量较大时优先选方案1，分层聚合能避免关联后中间结果集过度膨胀，查询性能更好
• 表数据量较小时优先选方案2，语法更简洁，维护成本更低

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