How to do a GROUP BY and COUNT within json_build_object() in Postgres

Question

I want to perform multiple GROUP BY and COUNT operations on a table(or CTE) and have the output as a singe JSON.

Consider the following table in a Postgres database:

Name	Author	Publisher	Language
Book1	Jason	Penguin	English
Book2	Jason	Macmillan	English
Book3	Paul	Macmillan	English
Book4	Julia	Macmillan	English
Book5	Julia	Penguin	English

This is my current SQL query

WITH first_selection AS (
    SELECT *
    FROM books
    where language='English')
SELECT json_build_object('author_options', json_agg(DISTINCT(author)),
                         'publisher_options', json_agg(DISTINCT(publisher)))
FROM first_selection

For which I get this output:

{
    "author_options":["Jason","Paul","Julia"],
    "publisher_options":["Penguin,"Macmillan"]
}

The problem is I also need the count of books for each publisher but I keep getting an error saying that nested aggregations are not allowed.

I need the count of books in the JSON output. Not necessarily in any specific structure, but the information needs to be there. Basically I want an output that looks something like this:

{
    "author_options":["Jason","Paul","Julia"],
    "publisher_options":["Penguin,"Macmillan"],
    "publisher_details": {
                             "Penguin": 2,
                             "Macmillan": 3
                          }
}

How do I count the number of books per publisher and put the result into the JSON?

Answer 1

You can aggregate the publishers in a separate step and then include that into the final result:

with first_selection as (
  select *
  from books
  where language = 'English'
), pub_info as (
  select json_object_agg(publisher, cnt) as details
  from (
    select publisher, count(*) as cnt
    from first_selection
    group by publisher
  ) t
)
SELECT json_build_object('author_options', json_agg(distinct author),
                         'publisher_options', json_agg(distinct publisher),
                         'publisher_details', (select details from pub_info))
FROM first_selection

Online example

Answer 2

 create table books (Name varchar(50), Author varchar(50), Publisher varchar(50), Language varchar(50));
 
 insert into books values('Book1',  'Jason',    'Penguin',      'English');
 insert into books values('Book2',  'Jason',    'Macmillan',    'English');
 insert into books values('Book3',  'Paul',     'Macmillan',    'English');
 insert into books values('Book4',  'Julia',    'Macmillan',    'English');
 insert into books values('Book5',  'Julia',    'Penguin',      'English');

Query:

 WITH first_selection AS (
     SELECT *
     FROM books
     where language='English')
 , publisherscount as
 (
 select Publisher, count(*) pcount
 from books
 group by publisher
 )
 SELECT json_build_object('author_options', json_agg(DISTINCT author),
                          'publisher_options', json_agg(DISTINCT publisher),
                          'publisher_details',(select   array_to_json(array_agg(json_build_object(publisher,pcount)))
                          from publisherscount)
                         )
 FROM first_selection

Output:

json_build_object
{author_options : [Jason, Julia, Paul], publisher_options : [Macmillan, Penguin], publisher_details : [{Macmillan : 3},{Penguin : 2}]}

db<fiddle here