NULLS in ARRAY_AGG and Checking for Nulls in Array (Postgres 9.5.1)

Question

I am using ARRAY_AGG on a field with potentially NULL values and I then want to check the array later for any NULL entires. For example:

WITH test AS
(
SELECT 'A' AS call_id, NULL AS outcome
UNION ALL
SELECT 'A' AS call_id, 'success' AS outcome
)

SELECT *
  FROM (
        SELECT call_id, ARRAY_AGG(outcome) AS outcome
          FROM test
          GROUP BY call_id
       ) AS sub
  WHERE outcome && ARRAY[NULL]

But the result of this query is no rows are returned. The ARRAY_AGG will create one row with call_id = 'A' and the outcome array equal to {,'success'}. How do I check this array for NULL values?

maybe this will help: http://stackoverflow.com/questions/34848009/check-if-null-exists-in-postgres-array/34848472#34848472 — verhie, Oct 20 '16 at 06:34
It is funny, but `... where array_position(outcome, null) is not null` should work. — Abelisto, Oct 20 '16 at 07:35
Take a look at: https://stackoverflow.com/questions/34848009/check-if-null-exists-in-postgres-array -1 = ANY(_product_id) IS NULL Might be what you are looking for — Dan, Mar 16 '18 at 17:01

score 0 · Answer 1 · 2016-10-20T06:31:45.467

Apart from writing a function, there is no way I can think of that will do this directly with the array. But what about counting the NULL values during aggregation?

WITH test (call_id, outcome) AS
(
  values 
    ('A', null), 
    ('A', 'success'), 
    ('B', 'failure')
)
SELECT *
FROM ( 
  SELECT call_id, 
         array_agg(outcome) AS outcome, 
         count(case when outcome is null then 1 end) as null_count
  FROM test
  GROUP BY call_id
) AS sub
where null_count > 0;

You can also integrate that directly into the select statement using a having clause:

SELECT call_id, 
       array_agg(outcome) AS outcome
FROM test
GROUP BY call_id
having count(case when outcome is null then 1 end) > 0

Another option would be to replace the NULL values with something that can't be in the outcome column and then check for that "magic" value.

score 0 · Answer 2 · answered Oct 20 '16 at 06:32

You could try un-nesting your outcome array and checking it for the existence of null values. Like so:

WITH test AS
(
  SELECT 'A' AS call_id, null AS outcome
  UNION ALL
  SELECT 'A' AS call_id, 'success' AS outcome
)
SELECT *
FROM (
  SELECT call_id, ARRAY_AGG(outcome) AS outcome
  FROM test
  GROUP BY call_id
) AS sub
WHERE EXISTS (select * from unnest(outcome) where unnest is null)

NULLS in ARRAY_AGG and Checking for Nulls in Array (Postgres 9.5.1)

2 Answers2