多表连接时如何避免json_agg聚合出现重复行？

问题原因 #

你的查询出现重复条目，是因为同时关联education和experience表时产生了笛卡尔积：当候选人有2条教育记录和2条经验记录时，两张表关联后会生成2×2=4条组合行。json_agg会把这4行中的每条教育/经验数据都聚合进数组，最终导致每个数组出现重复的条目。

解决方案 #

要避免笛卡尔积，需要先对单个表按候选人聚合数据，再关联到主查询中，以下是两种可行写法：

方法1：子查询预聚合

SELECT 
    edu.education,
    exp.experience
FROM application
JOIN candidate ON application.candidate_id = candidate.id
LEFT JOIN (
    SELECT candidate_id, json_agg(education) as education
    FROM education
    GROUP BY candidate_id
) edu ON candidate.id = edu.candidate_id
LEFT JOIN (
    SELECT candidate_id, json_agg(experience) as experience
    FROM experience
    GROUP BY candidate_id
) exp ON candidate.id = exp.candidate_id
WHERE application.candidate_id = 2;

方法2：LATERAL子查询（PostgreSQL专属）

SELECT 
    edu.education,
    exp.experience
FROM application
JOIN candidate ON application.candidate_id = candidate.id
LEFT JOIN LATERAL (
    SELECT json_agg(education) as education
    FROM education
    WHERE education.candidate_id = candidate.id
) edu ON true
LEFT JOIN LATERAL (
    SELECT json_agg(experience) as experience
    FROM experience
    WHERE experience.candidate_id = candidate.id
) exp ON true
WHERE application.candidate_id = 2;

效果说明 #

两种方法都会先单独对education和experience表按候选人ID聚合生成JSON数组，再和主查询关联，彻底避免了笛卡尔积的产生，最终每个数组只会保留对应候选人的真实记录数（各2条）。

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