How to specify a limit on Postgres json_agg

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?

Answer

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

Advertisement

Answer