I am using this function ARRAY_AGG in snowflake sql.
It is giving me [] empty array many times as I have null values there. It is only giving it when i am using case when inside.
how can i get just a null if the array is empty.?
array_agg(case when col1='abc' then object_construct('name',NAME) end) as names_list
It is default behavior of ARRAY_AGG:
Returns the input values, pivoted into an array. If the input is empty, the function returns an empty array.
NULL values are omitted from the output.
SELECT ARRAY_AGG(NULL);
-- []
SELECT ARRAY_AGG(col)
FROM VALUES (1),(2) AS s(col)
WHERE FALSE;
-- []
To return NULL
instead of an empty array a conditional logic needs to be applied:
SELECT CASE WHEN ARRAY_AGG(<expr>) != [] THEN ARRAY_AGG(<expr>) END
FROM tab;
or more compact NULLIF
:
SELECT NULLIF(ARRAY_AGG(<expr>), [])
FROM tab;