How to specify a limit on Postgres json_agg

Question

I want a JSON output having the distinct values of a column and also a limited number of rows.

This is the sample table that I have in a Postgres Database:

Name       Author     Copies Sold
----       -------     -----------
Book1      James           10
Book2      James           10
Book3      Alex            12
Book4      James           11
Book5      Amanda          1

I want to write an SQL query that returns a list of all the unique author names and also every row but with a limit of 3

This is the SQL query that I have so far

WITH first_query AS(
    SELECT * FROM sample_table LIMIT 3
)
SELECT json_build_object("all_authors",json_agg(DISTINCT(author)),
                         "book_details",json_agg(row_to_json(first_query))
)
FROM first_query;

This gives me the following output:

{"all_authors":["James","Alex"],
 "book_details":[{"name":"Book1","author":"James","copies sold":10},
                 {"name":"Book2","author":"James","copies sold":10},
                 {"name":"Book3","author":"Alex","copies sold":12}]}

In the above output, the only Authors in the list are James and Alex. However, I want the names of all three authors but still limiting "book_details" to the first three. i.e. I want Amanda to be on the list too.

Basically, this is the output I want:

{"all_authors":["James","Alex", "Amanda"],
 "book_details":[{"name":"Book1","author":"James","copies sold":10},
                 {"name":"Book2","author":"James","copies sold":10},
                 {"name":"Book3","author":"Alex","copies sold":12}]}

How do I get all distinct values of a column and still have a limit on the query?

eshirvana · Accepted Answer · 2021-04-28 20:25:51Z

3

here is how you can do it;

with cte as (
SELECT * FROM books limit 3
) 
SELECT json_build_object('all_authors',json_agg(DISTINCT(author)),'book_details',(select json_agg(row_to_json(cte.*,true)) from cte))
FROM books

answered Apr 28, 2021 at 20:25

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

How to specify a limit on Postgres json_agg

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related