By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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');
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
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
| json_build_object |
|---|
| {"author_options" : ["Jason", "Julia", "Paul"], "publisher_options" : ["Macmillan", "Penguin"], "publisher_details" : [{"Macmillan" : 3},{"Penguin" : 2}]} |
SELECT 1