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?.
drop table if exists person;
create table person(person_id int primary key, person_name text);
insert into person values
(1, 'John'),
(2, 'Jill'),
(3, 'Mary');
drop table if exists pet;
create table pet(pet_id int primary key, owner_id int, pet_name text);
insert into pet values
(1, 1, 'Fluffy'),
(2, 1, 'Buster'),
(3, 2, 'Doggy');
DROP TABLE
CREATE TABLE
INSERT 0 3
DROP TABLE
CREATE TABLE
INSERT 0 3
select
person_id,
jsonb_agg(to_jsonb(pet) - 'owner_id'),
person_name
from person
left join pet on person_id = owner_id
group by person_id;
| person_id | jsonb_agg | person_name |
|---|---|---|
| 1 | [{"pet_id": 1, "pet_name": "Fluffy"}, {"pet_id": 2, "pet_name": "Buster"}] | John |
| 2 | [{"pet_id": 3, "pet_name": "Doggy"}] | Jill |
| 3 | [null] | Mary |
SELECT 3