PostgreSQL: Handling NULL values in array aggregation with GROUP BY
Quick question that's been bugging me - Quick question that's been bugging me - I'm working with PostgreSQL 14 and trying to aggregate values into an array while grouping by a certain identifier. The scenario arises when there are NULL values in the data. I expect the aggregated array to exclude those NULL values, but it seems to include them instead. Here’s a simplified version of my query: ```sql SELECT id, ARRAY_AGG(value) AS values_array FROM my_table GROUP BY id; ``` In my dataset, I have records like this: | id | value | |----|--------| | 1 | A | | 1 | NULL | | 1 | B | | 2 | NULL | | 2 | C | When I run the query, I get this output: | id | values_array | |----|--------------| | 1 | {A,NULL,B} | | 2 | {NULL,C} | I want the `values_array` to look like `{A,B}` for ID 1 and `{C}` for ID 2, effectively filtering out the NULL values. I tried modifying the `ARRAY_AGG` function like this: ```sql SELECT id, ARRAY_AGG(value) FILTER (WHERE value IS NOT NULL) AS values_array FROM my_table GROUP BY id; ``` However, I'm still getting the NULL values included in the result. I’ve verified that the `FILTER` clause is placed correctly, but it seems ineffective here. Could it be that I'm using an older version of PostgreSQL that doesn't support this feature? Or is there a mistake in the query logic? Any help to resolve this scenario would be greatly appreciated! For reference, this is a production REST API.