ARRAY_CONTAINS

Test whether an array contains a value.

Category: collectionReturns: BOOLEANDialect: Standard

Syntax

ARRAY_CONTAINS(array, value)

Description

## Overview Returns true if the array contains at least one element equal to the search value, false otherwise. ARRAY_CONTAINS is the canonical membership predicate for array-typed columns and is the building block for tag-based filters, allow-list checks, and set-membership joins. Typical uses include WHERE clauses that filter rows by whether a tag or flag appears in an array column, HAVING clauses that qualify groups by membership in an aggregated ARRAY_AGG result, and join conditions that match a scalar key against a multi-valued attribute. ## Behavior - Returns BOOLEAN (true, false, or NULL) depending on whether the target is found. - Comparison uses SQL equality (=). Type coercion follows the engine's implicit promotion rules. - Scan is linear in the length of the array; no index is required. - For complex element types (STRUCT, MAP, ARRAY), equality is structural: two values match when all corresponding fields or elements are equal. - Works in WHERE, HAVING, CASE expressions, and projections exactly like any other boolean expression. - Does not short-circuit across rows; evaluation is per-row. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns false for any target (including NULL). - If the target value is NULL and the array contains at least one NULL element, returns true. - If the target value is NULL and the array has no NULLs, returns NULL (three-valued logic: existence cannot be confirmed). - If the target is non-NULL and the array has NULL elements but no matching value, the result is NULL, not false, because the NULL elements could hypothetically match. Apply ARRAY_COMPACT before the check to force a definite false when that behaviour is desired. ## Compatibility - Matches the standard SQL ARRAY_CONTAINS convention. The infix operator value = ANY(array) is accepted as an equivalent form. - Behaviour of NULL targets follows SQL three-valued logic, consistent with the IN predicate.

Parameters

Name	Type	Description
`array`		Specifies the array to scan for the target value.
`value`		Specifies the value to search for. Must be type-compatible with the array's element type.

Examples

-- Value present
SELECT ARRAY_CONTAINS(ARRAY[1, 2, 3], 2);  -- true

-- Filter sessions whose tag array contains 'vip'
SELECT session_id
FROM analytics.events.user_sessions
WHERE ARRAY_CONTAINS(tags, 'vip');

-- Value absent
SELECT ARRAY_CONTAINS(ARRAY[1, 2, 3], 5);  -- false

-- Searching for NULL returns true only when a NULL element exists
SELECT ARRAY_CONTAINS(ARRAY[1, CAST(NULL AS INT), 3], CAST(NULL AS INT));  -- true

-- Three-valued logic: target not found, but array has a NULL element
SELECT ARRAY_CONTAINS(ARRAY[1, CAST(NULL AS INT), 3], 5);  -- NULL

-- Join fact table to a lookup by tag membership
SELECT o.order_id
FROM analytics.events.user_sessions s
JOIN sales.ops.orders o ON o.user_id = s.user_id
WHERE ARRAY_CONTAINS(s.tags, 'checkout_abandoned');

Pitfalls

When the target is not present but the array contains at least one NULL element, ARRAY_CONTAINS returns NULL rather than false (SQL three-valued logic). Wrap with COALESCE or apply ARRAY_COMPACT if you need a definite boolean.
NULL input array returns NULL, not false. Filters such as WHERE ARRAY_CONTAINS(col, 'x') silently drop rows with NULL arrays; add OR col IS NOT NULL explicitly if those rows matter.
Comparison is structural for STRUCT and nested types. A target struct with NULL fields only matches an array element that has NULL in exactly the same fields.
ARRAY_CONTAINS is O(n) per row. For very wide arrays or high-selectivity filters, consider an auxiliary bitmap or an EXPLODE-and-join design.
Implicit casts can surprise: ARRAY_CONTAINS(ARRAY[1, 2, 3], '2') may either cast '2' to INT (matching) or raise a type error depending on session settings.