Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL left join query object array aggregate

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'
like image 460
Tom Berghuis Avatar asked Oct 07 '19 10:10

Tom Berghuis


People also ask

What is aggregate function in C++?

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.)

What is aggregate result in SQL?

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.

Why does the left join return a null value?

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.

What is the difference between general-purpose and aggregate functions?

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.


3 Answers

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:

  • Multiple array_agg() calls in a single query
  • Two SQL LEFT JOINS produce incorrect result

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:

  • Return multiple columns of the same row as JSON array of objects

Related:

  • What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
like image 71
Erwin Brandstetter Avatar answered Oct 17 '22 00:10

Erwin Brandstetter


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.

like image 7
klin Avatar answered Oct 17 '22 02:10

klin


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;
  1. FILTER clause to filter out NULL values. That creates a NULL value for Mary.
  2. If you want to add an empty JSON array: Use COALESCE, which replaces NULL with a default value
like image 3
S-Man Avatar answered Oct 17 '22 00:10

S-Man