含两个LATERAL JOIN的查询中json_agg()出现重复行问题

你遇到的重复问题，本质是多表JOIN产生了笛卡尔积导致的。让我给你拆解一下：

你的原查询同时把comment和tasklink左连接到task表，这两个表之间没有直接的关联条件，所以数据库会生成它们的笛卡尔积——1条comment记录 × 5条tasklink记录 = 5条中间结果行。当你用json_agg对这5行进行聚合时，comments数组会把同一个comment对象重复5次，这显然不是你想要的结果。

正确的解决方案：提前聚合关联表数据 #

避免笛卡尔积的核心思路是：先对每个关联表（comment和tasklink）按taskId聚合好JSON数组，再和主表task连接。这里有两种常用的写法：

方法1：使用LATERAL子查询（清晰直观）

SELECT 
    task.id,
    -- 没有评论时返回空数组，避免NULL
    COALESCE(c.comments, '[]'::json) AS comments,
    COALESCE(u.users, '[]'::json) AS users
FROM task
LEFT JOIN LATERAL (
    -- 先聚合当前task的所有评论
    SELECT json_agg(json_build_object('id', id, 'user', comment)) AS comments
    FROM comment
    WHERE comment.taskId = task.id
) c ON true
LEFT JOIN LATERAL (
    -- 先聚合当前task的所有关联用户链接
    SELECT json_agg(json_build_object('type', type, 'user', userid)) AS users
    FROM tasklink
    WHERE tasklink.taskId = task.id
) u ON true
WHERE task.id = 10;

方法2：使用关联子查询（更简洁）

如果觉得LATERAL子查询有点繁琐，也可以直接在SELECT子句里写关联子查询，效果完全一致：

SELECT 
    task.id,
    (SELECT json_agg(json_build_object('id', id, 'user', comment)) 
     FROM comment WHERE taskId = task.id) AS comments,
    (SELECT json_agg(json_build_object('type', type, 'user', userid)) 
     FROM tasklink WHERE taskId = task.id) AS users
FROM task
WHERE task.id = 10;

为什么原查询会失败？ #

再回头看你的原查询逻辑：

-- 简化后的原查询结构
SELECT task.id, 
       json_agg(...) AS comments, 
       json_agg(...) AS users 
FROM task 
LEFT JOIN comment c ON c.taskId = task.id
LEFT JOIN tasklink b ON b.taskId = task.id
GROUP BY task.id;

当你同时左连接comment和tasklink时，数据库会生成task × comment × tasklink的笛卡尔积。对于你的数据来说，就是1×1×5=5行数据，每行都包含同一个comment和不同的tasklink。json_agg会把这5行里的所有comment都聚合进去，所以最终comments数组里会有5个完全相同的对象，这就是重复的根源。

不推荐的临时方案（慎用） #

如果你临时想快速修复，也可以在json_agg里加上DISTINCT，但这只适用于确实没有重复评论的场景——如果你的comment表真的有两条完全相同的记录，这个方法会错误地合并它们：

SELECT 
    task.id,
    json_agg(DISTINCT json_build_object('id', c.id, 'user', c.comment)) AS comments,
    json_agg(json_build_object('type', b.type, 'user', b.userid)) AS users
FROM task
LEFT JOIN comment c ON c.taskId = task.id
LEFT JOIN tasklink b ON b.taskId = task.id
WHERE task.id = 10
GROUP BY task.id;

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