How to Avoid Row Multiplication When Using Multiple `array_agg()` Calls in SQL Joins?

Resolving Multiple array_agg() Calls in a Single Query

When working with multiple joins involving arrays, it is essential to understand their impact on the results. In this case, the original query attempts to extract arrays for both addresses and working days for each employee. However, the unexpected multiplication of rows due to multiple joins leads to incorrect aggregation.

Solution 1: Aggregate First, Join Later

To resolve this, an effective approach is to aggregate the data in subqueries before joining them. By separating the aggregation from the joins, the issue of row multiplication is avoided:

SELECT e.name, e.age, ad.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  -- PK covers whole row
) e
JOIN workingdays wd ON wd.employeeid = e.id
GROUP BY e.id, e.name, e.age;

Solution 2: Correlated Subqueries or JOIN LATERAL

For selective filtering on employees, correlated subqueries can be utilized:

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';  -- very selective

Alternatively, LATERAL joins can be employed in Postgres 9.3 or later:

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';  -- very selective

These alternative approaches ensure that all qualifying employees are retained in the result, avoiding the previous issue of row multiplication.

The above is the detailed content of How to Avoid Row Multiplication When Using Multiple `array_agg()` Calls in SQL Joins?. For more information, please follow other related articles on the PHP Chinese website!