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 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'
General-Purpose Aggregate Functions Collects all the input values, including nulls, into an array. Concatenates all the input arrays into an array of one higher dimension. (The inputs must all have the same dimensionality, and cannot be empty or null.)
In each case, the aggregate's result is the value that the associated window function would have returned for the “hypothetical” row constructed from args, if such a row had been added to the sorted group of rows represented by the sorted_args.
This result is due to the nature of the LEFT JOIN. Here is an example: So COUNT (*) is counting a "1" for Coyote Springs because the LEFT JOIN is returning a row with NULL values. Remember that in COUNT (*), a row with NULLs still counts.
Aggregate functions that support Partial Mode are eligible to participate in various optimizations, such as parallel aggregation. Table 9.57. General-Purpose Aggregate Functions Collects all the input values, including nulls, into an array. Concatenates all the input arrays into an array of one higher dimension.
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)
Db<>fiddle.
demo:db<>fiddle
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 valueIf you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With