ARRAY_COMPACT
Removes all NULL values from an array.
Syntax
ARRAY_COMPACT(array)
Parameters
Return Type
Array without NULL values.
Notes
This function works with both standard array types and variant array types.
Examples
Example 1: Removing NULLs from a Standard Array
SELECT ARRAY_COMPACT([1, NULL, 2, NULL, 3]);
Result:
[1, 2, 3]
Example 2: Removing NULLs from a Variant Array
SELECT ARRAY_COMPACT(PARSE_JSON('["apple", null, "banana", null, "orange"]'));
Result:
["apple", "banana", "orange"]
Example 3: Array with No NULLs
SELECT ARRAY_COMPACT([1, 2, 3]);
Result:
[1, 2, 3]