SQL查询中product_options返回重复数据的问题咨询

我仅有2条product_options记录，但执行以下SQL查询后，product_options数组中却返回了4条重复数据（重复2次）。关联了product_tags表，使用json_agg进行数据聚合，SQL代码如下：

SELECT 
      p.id,
      p.shop_id,
      p.provider_id,
      p.name,
      p.status,
      
      json_agg(json_build_object(
        'id', pt.id,
        'tag', pt.tag
      )) AS tags,
      
      json_agg(json_build_object(
        'id', po.id,
        'name', po.name
      )) AS product_options
      
      FROM product p
      
      LEFT JOIN product_options po
      ON po.p_id = p.id
      
      LEFT JOIN product_tags pt
      ON pt.p_id = p.id
      
      WHERE p.id = 1
      
      GROUP BY
      p.id

请问为何会出现这种情况？

这是多表关联产生笛卡尔积导致的：

• 当你同时左连接product_options和product_tags时，数据库会先将product与product_options的匹配记录关联，再把这个结果和product_tags的匹配记录做关联。
• 假设product_tags里有2条记录，product_options里有2条记录，关联后会生成2*2=4条中间结果行。
• 后续用json_agg聚合时，会把这4行里的product_options数据全部聚合进去，原本2条的记录就变成了4条（每条重复2次）。

可以通过以下几种方式解决：

方法1：用DISTINCT直接去重 #

在json_agg中添加DISTINCT，过滤重复的聚合对象：

SELECT 
      p.id,
      p.shop_id,
      p.provider_id,
      p.name,
      p.status,
      
      json_agg(DISTINCT json_build_object(
        'id', pt.id,
        'tag', pt.tag
      )) AS tags,
      
      json_agg(DISTINCT json_build_object(
        'id', po.id,
        'name', po.name
      )) AS product_options
      
      FROM product p
      
      LEFT JOIN product_options po
      ON po.p_id = p.id
      
      LEFT JOIN product_tags pt
      ON pt.p_id = p.id
      
      WHERE p.id = 1
      
      GROUP BY
      p.id

方法2：子查询提前聚合 #

分别在子查询中完成product_options和product_tags的聚合，再与product关联，从根源避免笛卡尔积：

SELECT 
      p.id,
      p.shop_id,
      p.provider_id,
      p.name,
      p.status,
      t.tags,
      po.product_options
FROM product p
LEFT JOIN (
    SELECT p_id, json_agg(json_build_object('id', id, 'tag', tag)) AS tags
    FROM product_tags
    GROUP BY p_id
) t ON t.p_id = p.id
LEFT JOIN (
    SELECT p_id, json_agg(json_build_object('id', id, 'name', name)) AS product_options
    FROM product_options
    GROUP BY p_id
) po ON po.p_id = p.id
WHERE p.id = 1

方法3：使用LATERAL JOIN #

利用LATERAL JOIN分别处理每个关联表的聚合，同样避免笛卡尔积：

SELECT 
      p.id,
      p.shop_id,
      p.provider_id,
      p.name,
      p.status,
      t.tags,
      po.product_options
FROM product p
LEFT JOIN LATERAL (
    SELECT json_agg(json_build_object('id', id, 'tag', tag)) AS tags
    FROM product_tags pt
    WHERE pt.p_id = p.id
) t ON true
LEFT JOIN LATERAL (
    SELECT json_agg(json_build_object('id', id, 'name', name)) AS product_options
    FROM product_options po
    WHERE po.p_id = p.id
) po ON true
WHERE p.id = 1

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