snowflake-cloud-data-platformarray-agg

snowflake ARRAY_AGG, replacing empty [] array by null


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

Solution

  • 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;