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 text, Author text, Publisher text, Language text);
CREATE TABLE
insert into books (name, author, publisher, language)
values
('Book1', 'Jason', 'Penguin', 'English'),
('Book2', 'Jason', 'Macmillan', 'English'),
('Book3', 'Paul', 'Macmillan', 'English'),
('Book4', 'Julia', 'Macmillan', 'English'),
('Book5', 'Julia', 'Penguin', 'English');
INSERT 0 5
with first_selection as (
select *
from books
where language = 'English'
), pub_info as (
select jsonb_object_agg(publisher, cnt) as details
from (
select publisher, count(*) as cnt
from first_selection
group by publisher
) t
)
select jsonb_pretty(jsonb_build_object('author_options', jsonb_agg(distinct author),
'publisher_options', jsonb_agg(distinct publisher),
'publisher_details', (select details from pub_info)))
from first_selection;
| jsonb_pretty |
|---|
| { "author_options": [ "Jason", "Julia", "Paul" ], "publisher_details": { "Penguin": 2, "Macmillan": 3 }, "publisher_options": [ "Macmillan", "Penguin" ] } |
SELECT 1