How to Avoid Redundant Data When Combining Multiple `array_agg()` Calls in a Single SQL Query?

Combining Multiple array_agg() Calls in a Single Query

In your query, you encounter an issue where multiple array_agg() calls return redundant data. The presence of duplicate rows due to multiple joins leads to undesired results. To resolve this, we can explore several approaches that avoid row multiplication from the outset.

Solution 1: Aggregating First, Joining Later

One effective method is to aggregate the data in subqueries before performing the joins. This ensures that each employee record is unique, preventing row multiplication. The modified query would look like this:

SELECT e.id, 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 / JOIN LATERAL

Another approach is to utilize correlated subqueries or JOIN LATERAL in PostgreSQL 9.3 or later. This technique allows you to retrieve the aggregated data for each employee within the main query. The modified queries would be:

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

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

Conclusion

By implementing these approaches, you can avoid row multiplication and obtain the desired result, which is an array of addresses and an array of working days for each employee. These solutions ensure that the data is aggregated appropriately and merged correctly, eliminating the need for additional filtering or post-processing.

The above is the detailed content of How to Avoid Redundant Data When Combining Multiple `array_agg()` Calls in a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!