CODEWITHSUNDEEP
sqlpostgresql
DevonDahon
DevonDahon

Reputation: 8350

How to exclude or remove NULL values from array and array_agg with PostgreSQL

In the following request:

SELECT
  myid,
  min(name) AS name,
  array_agg(array[zip, city])) AS city
FROM my_table
WHERE name LIKE 'Doe'
GROUP BY myid
LIMIT 10

I get the following result:

+-------+------+-------------------+
| myid  | name | city              |
+-------+------+-------------------+
| A123  | Doe  | {{69,"Groville"}} |
| B456  | Doe  | {{NULL,NULL}}     |
+-------+------+-------------------+

How can I get rid of the NULL values and get an empty field for city instead ?

=== EDIT ===

Replaced (array_agg(name))[1] AS name by min(name) AS name in the query, as suggested by @a_horse_with_no_name.

Upvotes: 5

Views: 6989

Answers (2)

user330315
user330315

Reputation:

You can use conditional aggregation. 

SELECT myid,
       min(name) AS name,
       array_agg(array[zip, city]) filter (where num_nulls(zip, city) = 0) AS city
FROM my_table
WHERE name LIKE 'Doe'
GROUP BY myid
LIMIT 10

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use array_remove():

array_agg(array_remove(array[zip, city], null))

Upvotes: 8

Related Questions