How to Efficiently Aggregate Multiple Arrays in a Single SQL Query?

Multiple array_agg() Calls in a Single Query: Aggregating Nested Arrays

In the realm of relational databases, the situation arises where multiple arrays need to be aggregated in a single query. This can be a tricky task, especially when there are joins involved.

The Issue

The challenge comes when attempting to aggregate arrays from different tables through multiple joins. The resulting array may contain unnecessary duplicates and inconsistencies. For instance, you may have a table of employees with multiple addresses (address) and working days (workingdays).

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

This query results in an array of street names and working days, but it duplicates the values multiple times.

The Solution: Aggregating First

The key to resolving this issue is to aggregate the arrays before performing the join. By aggregating first, you prevent the rows from multiplying unnecessarily.

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

In this query, the address aggregation is done in a subquery before joining it with the workingdays table. This ensures that only one set of street names and working days is associated with each employee.

Alternatively: Correlated Subqueries and JOIN LATERAL

For selective filtering on employees, correlated subqueries or JOIN LATERAL (in Postgres 9.3 or later) can be employed:

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

JOIN LATERAL can also be used in Postgres:

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 queries will retain all qualifying employees in the result.

The above is the detailed content of How to Efficiently Aggregate Multiple Arrays in a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!