CODEWITHSUNDEEP
sqljsonpostgresqlcommon-table-expression
Vinay
Vinay

Reputation: 759

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

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?

Upvotes: 4

Views: 3057

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 2

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

 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

Upvotes: 2

Related Questions