PostgreSQL聚合函数无法嵌套报错 如何编写返回嵌套JSON的查询

问题根因说明 #

• 「aggregate function calls cannot be nested」报错是因为PostgreSQL不允许直接嵌套json_agg这类聚合函数，直接在聚合函数内嵌套另一层聚合逻辑就会触发该错误
• 子查询执行慢、服务数据重复的问题，通常是因为一对多表关联后未做分组去重、关联字段缺失索引、子查询逻辑冗余导致

前提表结构说明 #

以下示例基于常规业务表结构：

1. vehicles表：主键id，存储车辆基础信息（如车牌号license_plate、车型model等）
2. services表：主键id，外键vehicle_id关联vehicles.id，存储服务信息（服务名称name、时长duration等）
   可根据实际业务字段调整查询内容

标准正确写法 #

方案1：LATERAL子查询写法（适配所有支持LATERAL的PG版本，灵活度最高） #

该写法天然避免一对多关联导致的车辆数据重复，不会出现重复服务数据，支持针对单辆车加动态过滤条件：

SELECT json_build_object(
  'vehicles', json_agg(
    json_build_object(
      'id', v.id,
      'license_plate', v.license_plate,
      'model', v.model,
      -- 补充其他需要返回的车辆字段
      'services', COALESCE(s.service_list, '[]'::json)
    )
  )
) AS final_result
FROM vehicles v
LEFT JOIN LATERAL (
  SELECT json_agg(
    json_build_object(
      'name', s.name,
      'duration', s.duration
      -- 补充其他需要返回的服务字段
    )
  ) AS service_list
  FROM services s
  WHERE s.vehicle_id = v.id
  -- 服务维度的过滤条件可以加在这里，比如只查未删除的服务：AND s.is_deleted = false
) s ON true
-- 车辆维度的过滤条件可以加在这里，比如只查运营中车辆：AND v.status = 'operating'
WHERE v.is_deleted = false;

其中COALESCE用于处理无服务的车辆，返回空数组而非null，符合前端数据解析习惯。

方案2：子查询预聚合写法（适合无动态过滤的场景，语法更简洁） #

如果不需要针对单辆车做服务维度的动态过滤，可以用预聚合的方式实现，性能和方案1一致：

SELECT json_build_object(
  'vehicles', json_agg(
    json_build_object(
      'id', v.id,
      'license_plate', v.license_plate,
      'model', v.model,
      'services', COALESCE(s.services, '[]'::json)
    )
  )
) AS final_result
FROM vehicles v
LEFT JOIN (
  SELECT 
    vehicle_id,
    json_agg(json_build_object('name', name, 'duration', duration)) AS services
  FROM services
  WHERE is_deleted = false
  GROUP BY vehicle_id
) s ON v.id = s.vehicle_id
WHERE v.is_deleted = false;

性能优化方案 #

• 关联字段加索引：给services.vehicle_id建立索引，如果服务查询字段固定，可以建立覆盖索引避免回表，示例：CREATE INDEX idx_services_vehicle_query ON services(vehicle_id) INCLUDE (name, duration, is_deleted);
• 减少扫描范围：如果不需要返回全量车辆数据，增加车辆维度的过滤条件或者分页逻辑，限制聚合的车辆数量
• 过滤无效数据：如果不需要返回无服务的车辆，把LEFT JOIN改为JOIN，直接过滤掉无服务匹配的车辆，减少聚合计算量
• 避免全表扫描：不要在关联条件或者过滤条件中对vehicle_id、id这类索引字段使用函数运算，会导致索引失效

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