PostgreSQL聚合查询问题：JSON_AGG/ARRAY_AGG生成重复条目

问题根源 #

你遇到的核心问题是多表JOIN产生了笛卡尔积。比如一辆车对应4条car_images记录、1条car_contract记录、1条car_km记录，INNER JOIN后会生成4×1×1=4条重复的车辆基础数据。聚合时，durationContract和km会把唯一的合同、里程数据重复4次；而当你把ci.id、ck.id、cc.id加入GROUP BY，相当于按每条JOIN后的行分组，自然会出现大量重复的车辆行，完全偏离了按车辆分组的需求。

解决方案 #

方案1：子查询提前聚合关联表（最直观）

先对每个关联表按car_id单独聚合，再和主表JOIN，彻底避免笛卡尔积：

SELECT
  ca.id,
  ca.name,
  ca.image,
  -- 单车辆的基础属性无需聚合，直接构建JSON
  json_build_object(
    'fuel', ca.fuel,
    'engine', ca.engine,
    'type', ca.type
  ) AS details,
  ci.images,
  cc.durationContract,
  ck.km
FROM car_abo ca
-- 聚合图片数据
LEFT JOIN (
  SELECT car_id, ARRAY_AGG(image) AS images
  FROM car_images
  GROUP BY car_id
) ci ON ci.car_id = ca.id
-- 聚合合同数据
LEFT JOIN (
  SELECT car_id, json_agg(json_build_object('price', price, 'duration', duration)) AS durationContract
  FROM car_contract
  GROUP BY car_id
) cc ON cc.car_id = ca.id
-- 聚合里程数据
LEFT JOIN (
  SELECT car_id, json_agg(json_build_object('price', price, 'duration', duration)) AS km
  FROM car_km
  GROUP BY car_id
) ck ON ck.car_id = ca.id

注意：用LEFT JOIN替代INNER JOIN，避免因某个关联表无数据导致车辆信息被过滤；如果只需要有全量关联数据的车辆，再换回INNER JOIN。

方案2：使用LATERAL JOIN（灵活适配复杂过滤）

LATERAL JOIN会针对主表的每一行单独执行子查询，既避免笛卡尔积，又支持对关联表添加过滤条件（比如只取最新合同）：

SELECT
  ca.id,
  ca.name,
  ca.image,
  json_build_object(
    'fuel', ca.fuel,
    'engine', ca.engine,
    'type', ca.type
  ) AS details,
  ci.images,
  cc.durationContract,
  ck.km
FROM car_abo ca
LEFT JOIN LATERAL (
  SELECT ARRAY_AGG(image) AS images
  FROM car_images ci
  WHERE ci.car_id = ca.id
) ci ON true
LEFT JOIN LATERAL (
  SELECT json_agg(json_build_object('price', price, 'duration', duration)) AS durationContract
  FROM car_contract cc
  WHERE cc.car_id = ca.id
) cc ON true
LEFT JOIN LATERAL (
  SELECT json_agg(json_build_object('price', price, 'duration', duration)) AS km
  FROM car_km ck
  WHERE ck.car_id = ca.id
) ck ON true

额外优化 #

如果确认每个车辆在car_contract和car_km中只有1条记录，无需用json_agg，直接用json_build_object即可，效率更高：

-- 以合同为例，替换子查询部分
LEFT JOIN (
  SELECT car_id, json_build_object('price', price, 'duration', duration) AS durationContract
  FROM car_contract
) cc ON cc.car_id = ca.id

内容的提问来源于stack exchange，提问作者Ömer Ok