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

Question

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.

Unrelated, but: why not use min(name) instead of first aggregating all names and then pick one — user330315
– user330315, Commented May 8, 2020 at 11:46

Gordon Linoff · Accepted Answer · 2020-05-08 11:30:28Z

8

You can use array_remove():

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

answered May 8, 2020 at 11:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DevonDahon Over a year ago

I get this error: ERROR: cannot accumulate empty arrays, SQL state: 2202E.

score 6 · Accepted Answer · 2020-05-08 11:58:26Z

6

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

edited May 8, 2020 at 11:58

answered May 8, 2020 at 11:44

user330315

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related