ARRAY_CONTACT() returns empty array

Question

I am compiling a list of values per users from 2 different columns into a single array like:

with test  as (
    select 1 as userId, 'something' as value1, cast(null as string) as value2
    union all
    select 1 as userId, cast(null as string), cast(null as string)
)

select
       userId,
       ARRAY_CONCAT(
       ARRAY_AGG(distinct value1 ignore nulls ),
       ARRAY_AGG(distinct value2 ignore nulls )
           ) as combo,
from test
group by userId

Everything works one until ARRAY_AGG() but then the ARRAY_CONCAT() just won't have it and returns and empty array [] whereas I expect it to be ['something'].

I am at loss as to why this is happening and whether I can force a workaround here.

user_5221944 · Answer

I am at loss as to why this is happening ...

ARRAY_CONCAT function returns NULL if any input argument is NULL

... and whether I can force a workaround here

Use below workaround

select
  userid,
  array_concat(
    ifnull(array_agg(distinct value1 ignore nulls ), []),
    ifnull(array_agg(distinct value2 ignore nulls ), [])
  ) as combo,
from test
group by userid

if applied to sample data in your question - output is

ARRAY_CONTACT() returns empty array

1 Answers