How to exclude NULLs from ARRAY so query won't fail
Glad you asked! BigQuery supports IGNORE NULLS
and RESPECT NULLS
modifiers in some of the aggregate functions, including ARRAY_AGG
, so your query becomes
#standardSQL
SELECT ARRAY_AGG(x IGNORE NULLS) FROM UNNEST([1,NULL,2,3]) x
and it passes producing [1,2,3]
. More details are in the documentation.
Another interesting use-case would be if you do not want to lose that NULL elements but rather want to substitute it with some default value. For example -999
Below will do this
#standardSQL
SELECT ARRAY_AGG(IFNULL(x,-999)) FROM UNNEST([1,NULL,2,3]) x
And in case if you want distinct elements only -
#standardSQL
SELECT ARRAY_AGG(DISTINCT IFNULL(x,-999)) FROM UNNEST([1,NULL,2,3,1,NULL]) x
Complementing @Mikhail Berlyant's answer, sometimes you want to keep the NULL values somehow but can't use a placeholder. For example, if you want a boolean array with NULLs, where you can't substitute NULL for true
/false
.
A nice workaround is wrapping the values inside a STRUCT, then constructing the array:
#standardSQL
SELECT ARRAY_AGG(STRUCT(x)) AS struct_array FROM UNNEST([1,NULL,2,3]) x