0

Database: postgresql 12. I have a table people:

person_id | name
----------+-----
1         |John
2         |Paul
3         |Jim
...

Each person can have zero or more car insurance contracts, so I have another table car_contracts as follows:

contract_id | person_id | contract_number | date_from | insured_value | car_tag | .... (more fields)
------------+-----------+-----------------+-----------+---------------+---------+ 
1           | 1         | C99             |2020-01-01 | 5000          | ABC     | ....
2           | 1         | C02             |2020-03-01 | 4000          | XYZ     | ....
3           | 3         | C96             |2020-02-02 | 12000         | KKK     | ....
4           | 1         | C01             |2019-01-01 | 5500          | XXX     | ....
5           | 2         | C02             |2019-06-01 | 1000          | 666     | ....
6           | 3         | C03             |2020-02-20 | 11000         | KPX     | ....
...

Each person can also have zero or more power plants (or whatever; the point is, another table with different fields but still tied to people by means of person_id), so the table plants is like

plant_id | person_id | plant_name | power | address     | .... (more fields)
---------+-----------+------------+-------+-------------+ 
1        | 2         | plant01    |100    | 230 rue st. | ....
2        | 3         | jimsplant1 |50     | ...         | ....
3        | 3         | jimsplant2 |65.5   |             | ....
4        | 2         | plant02    |12.5   |             | ....
5        | 1         | house01    |20     |             | ....
6        | 1         | house02    |20     |             | ....
...

I can either get a list of people with their contracts or people with their plants by joining the people table to the car_contracts and plants table respectively. Is it possible to somehow get all the information at once (people with their contracts and their plants) with a single query?

The main issue seems to me that the number of contracts and the number of plants have no relation and can be very different, so I think joining people with contracts with plants makes little sense.

3
  • Can you elaborate a little? Do you want a list of all users and include information on plants and contracts where it exists, or only return the users who have plants/contracts? Can you include some example results?
    – HandyD
    Apr 19, 2020 at 22:33
  • You can join more than two tables, if that is what you are asking for. from people p join car_contracts cc on ... join plants pl on ...
    – user1822
    Apr 20, 2020 at 8:50
  • @HandyD I don't care about the exact format, as long as I can get all the information in a single query. Since this is postgres, I was thinking of each person having an array of contracts and an array of plants, but I'm not sure this can be done efficiently. But any format is fine, really.
    – homer5439
    Apr 20, 2020 at 10:01

1 Answer 1

1

Well, you can aggregate the rows into arrays:

select p.person_id, p.name, 
       array_agg(cc) as contracts,
       array_agg(pl) as plants
from people p
  left join car_contracts cc on p.person_id = cc.person_id
  left join plants pl on p.person_id = pl.person_id
group by p.person_id;

(This assumes that person_id is declared as the primary key)

Aggregating the rows into json values might be easier to work with in the front end:

select p.person_id, p.name, 
       json_agg(to_jsonb(cc)) as contracts,
       jsong_agg(to_jsonb(pl)) as plants
from people p
  left join car_contracts cc on p.person_id = cc.person_id
  left join plants pl on p.person_id = pl.person_id
group by p.person_id;

To avoid duplicates, do the aggregation in a derived table:

select p.person_id, p.name, 
       cc.contracts,
       pl.plants
from people p
  left join (
    select person_id, jsonb_agg(to_jsonb(c)) as contracts
    from car_contracts c
    group by person_id
  ) cc on p.person_id = cc.person_id
  left join (
    select person_id, jsonb_agg(to_jsonb(p)
    from plants 
    group by person_id
  ) pl on p.person_id = pl.person_id;
4
  • Brilliant. person_id is indeed the primary key on people. With my current figures (in the order of tens of thousand of entries in each table) this seems to run very fast. Thanks!
    – homer5439
    Apr 20, 2020 at 11:19
  • After careful inspection, I see to get duplicates in the contracts and plants arrays, I suspect due to the cartesian product?
    – homer5439
    Apr 20, 2020 at 18:43
  • 1
    @homer5439: there is no cartesian product in my query. But yes, if a person has multiple cars and contracts, there could be duplicates. You can avoid that by doing the aggregation in a derived table.
    – user1822
    Apr 20, 2020 at 20:32
  • Sorry, I really meant "the join between contracts and plants", not "cartesian product", my bad.
    – homer5439
    Apr 20, 2020 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.