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: stackoverflow.com/questions/34848009/… — verhie
– verhie, Commented Oct 20, 2016 at 6:34
It is funny, but ... where array_position(outcome, null) is not null should work. — Abelisto
– Abelisto, Commented Oct 20, 2016 at 7:35
Take a look at: stackoverflow.com/questions/34848009/… -1 = ANY(_product_id) IS NULL Might be what you are looking for — Dan
– Dan, Commented Mar 16, 2018 at 17:01

score 0 · Accepted Answer · 2016-10-20 06:31:45Z

0

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.

edited Oct 20, 2016 at 6:31

answered Oct 20, 2016 at 6:21

user330315

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

Comments

Chitharanjan Das · Accepted Answer · 2016-10-20 06:32:46Z

0

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)

answered Oct 20, 2016 at 6:32

Chitharanjan Das

1,35310 silver badges15 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related