Postgres jsonb_agg children cart
Selecting lists of related entities from RDBMS’es can be a chore at times. Especially when ORM’s aren’t used or desired. jsonb_agg of postgres can be a convenient workaround for these cases:
select orders.*,
(select jsonb_agg(joined_lines) from
(select *
from lines
where lines.order_id = orders.order_id
) joined_lines
) as lines
from orders
where ...
;Given we have a vanilla order management system with orders and their lines in separate tables:
| orders | ||
| id | customer_id | total_value |
| 1 | 1 | 15 |
| 2 | 2 | 10 |
| lines | |||
| line_id | order_id | value | |
| 1 | 1 | 10 | |
| 2 | 1 | 2 | |
| 3 | 1 | 3 | |
| 4 | 2 | 10 | |
The query above would return a nice representation:
| order_id | customer_id | total_value | lines (pretty-formated) |
| 1 | 1 | 15 | [
{"value": 10, "line_id": 1, "order_id": 1},
{"value": 2, "line_id": 2, "order_id": 1},
{"value": 3, "line_id": 3, "order_id": 1}
] |
| 2 | 2 | 10 | [{"value": 10, "line_id": 4, "order_id": 2}] |
Which we can conveniently process in our application code.
Warning: I am not a query optimization expert, so please examine your execution plan carefully before applying the technique. I saw minor deviations in costs for my production queries, but your mileage can differ.
Alternative: a simple join
A common way to eagerly select multiple orders with their lines would be to do a join:
SELECT *
from orders
inner join lines on orders.id = lines.order_id
WHERE ...This query would produce dataset along the lines of:
| order_id | customer_id | total_value | line_id | order_id | value |
| 1 | 1 | 15 | 1 | 1 | 10 |
| 1 | 1 | 15 | 2 | 1 | 2 |
| 1 | 1 | 15 | 2 | 1 | 3 |
| 2 | 2 | 10 | 4 | 2 | 10 |
Mapping onto the order - lines structure at the receiving side isn’t too complex, but can become a burden with the number of relations joined increasing.