postgresql 为什么json_agg()抱怨子查询返回多行？

vvppvyoh 于 2024-01-07 发布在 PostgreSQL

关注(0)|答案(2)|浏览(170)

我想选择每一个宠物品种计数，由他们的主人和类型分组。
我写了一个子查询，它返回宠物类型的品种计数，这几乎是我所需要的，除了我需要一个JSON数组中的结果。
| 按类型繁殖|
| --|
| {“type”：“CAT”，“breed_count”：[{“BENGAL”：1，“BURMESE”：2}]}|
| {“type”：“DUG”，“breed_count”：[{“GERMAN_SHEPHERD”：1，“秋田”：2}]}|
我希望json_agg（）函数能做到这一点，但它返回了一个错误：[21000] ERROR：一个用作表达式的子查询返回了不止一行。
以下是我的完整查询：

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数组中？

示例：d-fiddle

postgresql

来源：https://stackoverflow.com/questions/77678523/why-does-json-agg-complain-about-subquery-returning-more-than-one-row

2条答案

按热度按时间

pod7payv1#

在这里，它甚至更简单。

select jsonb_agg(t) from 
(
 select type, jsonb_object_agg(breed, cnt) breed_count 
 from 
 (
  select type, breed, count(*) cnt
  from pet
  group by type, breed
 ) t
 group by type
) t;

字符串
DB fiddle

赞(0）回复(0）举报 2024-01-07

zi8p0yeb2#

下面是另一种更优化的方法来处理jsonb_build_object和json_agg()：

select jsonb_build_object(
            'type',
            type,
            'breed_count',
            json_agg(jsonb_build_object(breed, cnt))
       ) as breed_by_type
            
from (
  select type, breed, count(*) as cnt
  from pet
  group by type, breed
) as s
group by type;

字符串
结果如下：

breed_by_type
--------------
{"type":"CAT","breed_count":[{"BURMESE":2},{"BENGAl":1}]}
{"type":"DOG","breed_count":[{"AKITA":2},{"GERMAN_SHEPHERD":1}]}

型
Demo here的

赞(0）回复(0）举报 2024-01-07

我来回答

postgresql 为什么json_agg()抱怨子查询返回多行？

2条答案

相关问题

热门标签

最新问答