How to Avoid Unexpected Results from Multiple Nested `array_agg()` Calls in PostgreSQL?

Avoid Multiple Nested Aggregates in PostgreSQL Queries

Multiple array_agg() calls in a single query can result in unexpected results, as seen in the example provided. This issue arises from joining tables with multiple rows, effectively creating a Cartesian product. To rectify this, consider the following strategies:

Aggregate First, Join Later

Aggregate the data from each table separately in a subquery before joining them. This ensures that you aggregate over a unique set of rows:

SELECT e.id, e.name, e.age, e.streets, array_agg(wd.day) AS days
FROM (
    SELECT e.id, e.name, e.age, array_agg(ad.street) AS streets
    FROM employees e
    JOIN address ad ON ad.employeeid = e.id
    GROUP BY e.id
) e
JOIN workingdays wd ON wd.employeeid = e.id
GROUP BY e.id, e.name, e.age;

Correlated Subqueries or JOIN LATERAL

Use correlated subqueries or JOIN LATERAL to aggregate data for each row individually, allowing for selective filters:

Correlated Subqueries:

SELECT name, age
    , (SELECT array_agg(street) FROM address WHERE employeeid = e.id) AS streets
    , (SELECT array_agg(day) FROM workingdays WHERE employeeid = e.id) AS days
FROM employees e
WHERE e.namer = 'peter';

JOIN LATERAL:

SELECT e.name, e.age, a.streets, w.days
FROM employees e
LEFT JOIN LATERAL (
    SELECT array_agg(street) AS streets
    FROM address
    WHERE employeeid = e.id
    GROUP BY 1
) a ON true
LEFT JOIN LATERAL (
    SELECT array_agg(day) AS days
    FROM workingdays
    WHERE employeeid = e.id
    GROUP BY 1
) w ON true
WHERE e.name = 'peter';

These methods prevent unnecessary row duplication and provide the desired array aggregation results.

The above is the detailed content of How to Avoid Unexpected Results from Multiple Nested `array_agg()` Calls in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!