Return a new array with all NULL elements removed.
ARRAY_COMPACT(array)## Overview Returns a new array in which every NULL element has been removed, preserving the original order of the remaining non-NULL values. ARRAY_COMPACT is the idiomatic way to scrub sparse arrays before aggregation, joining, or unnesting, so downstream consumers see only defined values. Typical uses include cleansing per-row tag or event arrays prior to EXPLODE (avoiding NULL-valued output rows), preparing inputs for aggregates that would otherwise treat NULL entries as valid domain values, and producing dense arrays for export formats that disallow NULLs in collection elements. ## Behavior - Produces an ARRAY<T> containing only the non-NULL elements in their original relative order. - Length of the result equals the count of non-NULL elements in the input. - Works on any element type, including nested arrays and structs. Struct elements are treated as non-NULL when the struct itself is non-NULL, even if some inner fields are NULL. - Never mutates the input array; a new array value is returned. - Leaves the order of the retained elements unchanged; it does not sort or deduplicate. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array of the same element type. - Array consisting entirely of NULL elements returns an empty array (not NULL). - Mixed arrays return only the non-NULL subset in their original positions. ## Compatibility - Equivalent in semantics to ARRAY_FILTER(array, x -> x IS NOT NULL) but does not require a lambda argument. - Matches the standard array/map SQL convention for element-level NULL scrubbing.
| Name | Type | Description |
|---|---|---|
array | Specifies the input array. The result is a new array containing only the non-NULL elements in their original relative order. |
-- Drop NULLs from a literal array
SELECT ARRAY_COMPACT(ARRAY[1, CAST(NULL AS INT), 2, CAST(NULL AS INT), 3]); -- [1, 2, 3]-- Scrub NULL tags from a per-session array column
SELECT session_id, ARRAY_COMPACT(tags) AS tags
FROM analytics.events.user_sessions;-- Array that is already NULL-free is returned unchanged
SELECT ARRAY_COMPACT(ARRAY['a', 'b', 'c']); -- ['a', 'b', 'c']-- Array of all NULLs collapses to an empty array
SELECT ARRAY_COMPACT(ARRAY[CAST(NULL AS INT), CAST(NULL AS INT)]); -- []-- NULL input returns NULL
SELECT ARRAY_COMPACT(CAST(NULL AS ARRAY<INT>)); -- NULL-- Combine with EXPLODE to avoid NULL rows downstream
SELECT user_id, event
FROM analytics.events.user_sessions
LATERAL VIEW EXPLODE(ARRAY_COMPACT(events)) t AS event;