为何json_agg()报错子查询返回多行？如何生成宠物统计JSON数组

问题概述 #

需要按主人维度，统计其名下每种宠物类型对应的各品种数量，并将结果整理为指定结构的JSON数组。预期输出的breed_by_type字段格式示例如下：

原查询尝试使用json_agg()实现需求，但触发错误：

[21000] ERROR: more than one row returned by a subquery used as an expression

原查询语句：

SELECT 
  o.id, 
  JSON_AGG(
    (
      SELECT 
        JSONB_BUILD_OBJECT(
          'type', 
          p1.type, 
          'breed_count', 
          JSONB_BUILD_ARRAY(
            JSONB_OBJECT_AGG(
              p1.breed, 
              (
                SELECT 
                  COUNT(p2.id) 
                FROM 
                  pet p2 
                WHERE 
                  p2.owner_id = p1.owner_id 
                  AND p2.breed = p1.breed 
                  AND p2.type = p1.type
              )
            )
          )
        ) AS breed_by_type 
      FROM 
        pet p1 
      GROUP BY 
        p1.type
    )
  ) 
FROM 
  owner o 
  JOIN pet p ON p.owner_id = o.id 
GROUP BY 
  o.id;

错误原因 #

报错根源在于：外层JSON_AGG()中嵌套的子查询是按p1.type分组的，会返回多行结果，但作为表达式使用的子查询要求只能返回单行数据，因此触发了上述错误。

正确解决方案 #

可以通过分层聚合的方式实现需求，先按主人、宠物类型、品种统计数量，再逐层构建JSON结构：

方案1：分步聚合 #

SELECT 
  o.id,
  JSON_AGG(
    JSONB_BUILD_OBJECT(
      'type', p.type,
      'breed_count', JSONB_BUILD_ARRAY(
        JSONB_OBJECT_AGG(p.breed, p.breed_count)
      )
    )
  ) AS breed_by_type
FROM owner o
JOIN (
  -- 先统计每个主人下，每种类型每个品种的数量
  SELECT 
    owner_id,
    type,
    breed,
    COUNT(id) AS breed_count
  FROM pet
  GROUP BY owner_id, type, breed
) p ON o.id = p.owner_id
GROUP BY o.id;

方案2：子查询内完成类型聚合 #

SELECT 
  o.id,
  JSON_AGG(breed_type_obj) AS breed_by_type
FROM owner o
JOIN (
  -- 直接在子查询中构建每个类型的完整JSON对象
  SELECT 
    owner_id,
    JSONB_BUILD_OBJECT(
      'type', type,
      'breed_count', JSONB_BUILD_ARRAY(
        JSONB_OBJECT_AGG(breed, COUNT(id))
      )
    ) AS breed_type_obj
  FROM pet
  GROUP BY owner_id, type
) p ON o.id = p.owner_id
GROUP BY o.id;

逻辑说明 #

1. 内层子查询：按owner_id和type分组，使用JSONB_OBJECT_AGG(breed, COUNT(id))生成该宠物类型下所有品种与对应数量的键值对对象；再用JSONB_BUILD_ARRAY将对象包裹成数组，结合type字段构建出单个类型的完整JSON结构。
2. 外层查询：使用JSON_AGG将每个主人对应的所有类型JSON结构聚合为一个数组，最终得到符合预期的结果。

内容的提问来源于stack exchange，提问作者Baksa Zoltán