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?

Unrelated to your problem but: distinct is not a function. Enclosing the column following the keyword is useless. — user330315
– user330315, Commented May 3, 2021 at 18:22

user330315 · Accepted Answer · 2021-05-03 18:41:00Z

2

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

answered May 3, 2021 at 18:41

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Vinay Over a year ago

Suppose I have multiple columns for which I want the count(like with publisher). Do i need to create separate steps for each? Say I have 50 other columns for which I want the count. Can they all be done in one step or do I need to write 50 steps like how you did with 'publisher'? (I can modify my question to include this if required)

user330315 Over a year ago

If you have 50 different ways to group, you will need 50 GROUP BY queries

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-05-03 18:20:53Z

 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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related