PostgreSQL: Aggregating Rows into Arrays
Have you ever performed a JOIN and got duplicated rows due to a One-to-Many relationship? This article discusses how to consolidate that data into a single row using Arrays.
1Case Study: Bus Fleet Tracking System
Suppose we have two tables: buses and coordinates (location history over time). If we do a standard JOIN, the result will produce multiple rows for the same bus.
Standard JOIN Result (Problem)
| bus_name | time | coordinate |
|---|---|---|
| Bus A | 10:00 | -6.20, 106.81 |
| Bus A | 10:05 | -6.21, 106.82 |
| Bus A | 10:10 | -6.22, 106.83 |
On the Backend/Frontend side, repeating data like "Bus A" can be annoying to loop over and render.
2Solution: ARRAY_AGG()
In PostgreSQL, we can solve this problem at the database level using the ARRAY_AGG() aggregate function combined with GROUP BY.
SELECT
b.bus_name,
ARRAY_AGG(c.coordinate ORDER BY c.time ASC) as coordinate_history
FROM buses b
JOIN coordinates c ON b.id = c.bus_id
GROUP BY b.id, b.bus_name;
Query Result using ARRAY_AGG()
| bus_name | coordinate_history |
|---|---|
| Bus A | {"-6.20, 106.81", "-6.21, 106.82", "-6.22, 106.83"} |
| Bus B | {"-6.30, 106.70", "-6.31, 106.71"} |
3Bonus: JSON_AGG() for Complex Objects
What if we also want to know when (time) the coordinates were recorded? A flat array isn't ideal for holding two properties. The solution is to use JSON_AGG() combined with JSON_BUILD_OBJECT().
SELECT
b.bus_name,
JSON_AGG(
JSON_BUILD_OBJECT(
'time', c.time,
'coordinate', c.coordinate
) ORDER BY c.time ASC
) as tracking_data
FROM buses b
JOIN coordinates c ON b.id = c.bus_id
GROUP BY b.id, b.bus_name;
As a result, the tracking_data column will directly produce a JSON Array of Objects. You can return this response directly from your Backend API to your Frontend extremely efficiently!