I have 2 tables:
Table person with columns: person_id, person_name
Table pet with columns: pet_id, owner_id, pet_name
person data:
1, 'John'
2, 'Jill'
3, 'Mary'
pet data:
1, 1, 'Fluffy'
2, 1, 'Buster'
3, 2, 'Doggy'
How to write a SELECT query from person left join pet on person_id = owner_id with aggregate functions so my result data looks like:
1, [{pet_id:1,pet_name:'Fluffy'},{pet_id:2,pet_name:'Buster'}], 'John'
2, [{pet_id:3,pet_name:'Doggy'}], 'Jill'
3, [],'Mary'
Use LEFT JOIN LATERAL and aggregate in the subquery:
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; -- optional, Q suggests ordered results
db<>fiddle here
This way you do not need to aggregate results from the outer query. Simpler and cleaner when your outer query is more complex than the example in the question. When aggregating multiple related tables, it even becomes a necessity:
It is also typically much faster when there are selective predicates on the outer table person - which is the typical use case.
Make sure there is an index on pet(owner_id) to make it fast.
Or even one on pet(owner_id, pet_id, pet_name) or pet(owner_id) INCLUDE (pet_id, pet_name) in Postgres 11 or later, if your row isn't wide like in your example, and if you get index-only scans out of it.
Oh, and use json_build_object() to preserve attribute names for arbitrary selections:
Related:
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
(3 rows)
select
COALESCE(
json_agg(row_to_json(row(p2.pet_id::text, p2.pet_name))) FILTER (WHERE pet_id IS NOT NULL),
'[]'
) as json,
p1.person_name
from person p1
left join pet p2
on p1.person_id = p2.owner_id
group by
p1.person_name;
FILTER clause to filter out NULL values. That creates a NULL value for Mary.COALESCE, which replaces NULL with a default valuePostgres' built in JSON and aggregation functions can handle your requirement:
select
json_agg(row_to_json(row(p2.pet_id::text, p2.pet_name))) as json,
p1.person_name
from person p1
left join pet p2
on p1.person_id = p2.owner_id
group by
p1.person_name;
json_agg() instead of array_agg()
FILTER (WHERE pet_id IS NOT NULL). If you want to create an empty array you can use COALESCE dbfiddle.uk/…some lateral query can also be written in correlated subquery.
You want to select some columns from tableA and aggregate info from tableB.
you can use select tableA columns, aggregate function from tableA.
It will work because aggregate functions in this context only generate one row.
Based on Erwin's answer. You can also:
SELECT
p.person_id,
p.person_name,
(
SELECT
coalesce(json_agg(json_build_object('pet_id', pet.pet_id, 'pet_name', pet_name)), '[]') AS pets
FROM
pet
WHERE
pet.owner_id = p.person_id)
FROM
person_p p;
