ARRAY_REMOVE

Return a new array with every occurrence of a value removed.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

ARRAY_REMOVE(array, element)

Description

## Overview Returns a new array with every element equal to the target value removed, preserving the original relative order of the remaining elements. ARRAY_REMOVE is the value-aware complement to ARRAY_COMPACT and the most direct way to drop a single known value (or all NULLs) from an array column. Typical uses include stripping sentinel tags from per-row tag arrays, removing heartbeat or noise events from an event trail, and cleaning input arrays before downstream aggregation. ## Behavior - Produces an ARRAY<T> whose length is the input length minus the count of matching elements. - Element comparison is structural and uses SQL equality. - When the target is NULL, every NULL element is removed. This is a direct override of ARRAY_CONTAINS's three-valued NULL semantics for this function. - Remaining elements retain their relative order. - Works on any element type supported by equality. - Never mutates the input. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array. - Passing NULL as the target removes all NULL elements; the result may still contain other values. - If the target value is not present, the input array is returned unchanged (structurally equivalent). ## Compatibility - Matches the standard array/map SQL convention for ARRAY_REMOVE. For NULL-only removal, ARRAY_COMPACT is a more explicit alias. - Equivalent to FILTER(array, x -> x IS DISTINCT FROM element), without requiring a lambda.

Parameters

Name	Type	Description
`array`		Specifies the input array. The result is a new array with all matching elements dropped; the input is never modified.
`element`		Specifies the value to remove. Every element equal to this value is removed from the result. Pass NULL to drop NULL elements specifically.

Examples

-- Remove every occurrence
SELECT ARRAY_REMOVE(ARRAY[1, 2, 3, 2, 1], 2);  -- [1, 3, 1]

-- No-op when the element is absent
SELECT ARRAY_REMOVE(ARRAY[1, 2, 3], 5);  -- [1, 2, 3]

-- Remove NULL elements explicitly
SELECT ARRAY_REMOVE(ARRAY[1, CAST(NULL AS INT), 2, CAST(NULL AS INT)], CAST(NULL AS INT));  -- [1, 2]

-- Strip a sentinel tag from per-session tag arrays
SELECT session_id, ARRAY_REMOVE(tags, 'internal_test') AS tags
FROM analytics.events.user_sessions;

-- NULL input returns NULL
SELECT ARRAY_REMOVE(CAST(NULL AS ARRAY<INT>), 1);  -- NULL

-- Combine with ARRAY_AGG to produce cleaned group-level collections
SELECT user_id, ARRAY_REMOVE(ARRAY_AGG(event_type), 'heartbeat') AS meaningful_events
FROM analytics.events.user_sessions
GROUP BY user_id;

Pitfalls

To remove NULLs, pass an explicitly-typed NULL as the target. A bare NULL without a cast can fail type inference.
ARRAY_REMOVE strips every occurrence, not just the first. Use a custom FILTER with a counter if only the first match should be dropped.
Comparison is structural. Two STRUCT values that differ only in NULL field positions are considered distinct and are not removed.
A NULL input array returns NULL. Coerce with COALESCE(col, ARRAY()) when NULL inputs should degrade to empty arrays.