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 person(person_id int primary key, person_name text);
insert into person values
(1, 'John'),
(2, 'Jill'),
(3, 'Mary');
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');
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
SELECT p.person_id, COALESCE(pet.pets, '[]') AS pets, p.person_name
FROM person p
LEFT JOIN LATERAL (
SELECT json_agg(json_build_object('pet_id', pet.pet_id
, 'pet_name', pet.pet_name)) AS pets
FROM pet
WHERE pet.owner_id = p.person_id
) pet ON true
ORDER BY p.person_id;
| person_id | pets | 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 | [] | Mary |
SELECT 3