How to Avoid Duplicate Rows When Using Multiple Array Aggregates in a SQL Query?

Avoid Multiple Array Aggregates in a Query

In your query, you're attempting to use multiple array_agg() functions within a single query to retrieve arrays from different tables. However, this approach leads to duplicate rows and distorted results.

The Issue

When you perform multiple joins and aggregate functions, the result set can be inflated with duplicates. In your case, joining the address and workingdays tables creates multiple rows for each employee, resulting in duplicate entries in the aggregated arrays.

Solution: Separate Aggregation and Joins

To resolve this issue, it's recommended to separate the aggregation operation from the join process. Consider the following approaches:

Aggregate First, Join Later:

First, aggregate the data from each table separately using subqueries. Then, join the aggregated results based on the primary key or a common column:

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:

For selective filtering of data, consider using correlated subqueries or LATERAL joins in PostgreSQL:

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 (PostgreSQL 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';

These approaches ensure that the aggregated arrays are correctly associated with the corresponding employees, providing the expected results.

The above is the detailed content of How to Avoid Duplicate Rows When Using Multiple Array Aggregates in a SQL Query?. For more information, please follow other related articles on the PHP Chinese website!