Question 307064 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why does json_agg() complain about subquery returning more than one row?

I want to select every pet breed count, grouped by their owner and type.

I’ve written a subquery that returns the breed count by pet type which is almost what i need, except i need the result in a json array.

breed_by_type
{"type": "CAT", "breed_count": [{"BENGAL": 1, "BURMESE": 2}]}
{"type": "DUG", "breed_count": [{"GERMAN_SHEPHERD": 1, "AKITA": 2}]}

I expected that the json_agg() function would do this but it returns an error: [21000] ERROR: more than one row returned by a subquery used as an expression.

Here is my full query:

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;

How can i collect the subquery’s result into a json array?

Example: d-fiddle

Tags:

2

Answers


  1. 0

    Here’s another, more optimal way to do it with jsonb_build_object and 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;

    Results :

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

    Demo here

    Login or Signup to reply.
  2. 0

    Here it is even simpler.

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search