ARRAY_SIZE

Return the element count of an array.

Category: collectionReturns: INTDialect: Standard

Syntax

ARRAY_SIZE(array)

Description

## Overview Returns the number of elements along the first dimension of the input array, including NULL elements. ARRAY_SIZE is the canonical length primitive and pairs naturally with ARRAY_LENGTH, CARDINALITY, and SIZE as portable synonyms. Typical uses include deriving a per-row count metric for a nested collection column, gating downstream work by minimum or maximum array length, and distinguishing empty arrays (length 0) from NULL arrays (length NULL) in reporting. ## Behavior - Returns an INT equal to the number of top-level elements. - NULL elements count toward the length; they are valid array positions. - Empty array returns 0. - NULL input returns NULL (this engine does not coerce NULL arrays to a zero count). - Runs in O(1); the length is stored with the array metadata. - For multi-dimensional arrays, returns only the length of the outermost dimension. Use ARRAY_LENGTH with an explicit dimension for inner axes. ## Null and empty handling - NULL input array returns NULL. - Empty array returns 0. - NULL elements inside the array are counted like any other element. - The result is always non-negative when non-NULL. ## Compatibility - Matches the array/map SQL convention for ARRAY_SIZE. SIZE and CARDINALITY are commonly-accepted synonyms. - For portable NULL handling, wrap with COALESCE(ARRAY_SIZE(col), 0) when callers treat NULL and empty identically.

Parameters

Name	Type	Description
`array`		Specifies the input array. The result is the count of elements along the first dimension, including NULL elements.

Examples

-- Size of a basic array
SELECT ARRAY_SIZE(ARRAY[1, 2, 3]);  -- 3

-- Empty array returns 0
SELECT ARRAY_SIZE(CAST(ARRAY() AS ARRAY<INT>));  -- 0

-- NULL elements count toward the length
SELECT ARRAY_SIZE(ARRAY[1, CAST(NULL AS INT), 3]);  -- 3

-- NULL input returns NULL
SELECT ARRAY_SIZE(CAST(NULL AS ARRAY<INT>));  -- NULL

-- Per-session trail length as a derived metric
SELECT session_id, ARRAY_SIZE(events) AS event_count
FROM analytics.events.user_sessions;

-- Filter sessions with long trails
SELECT session_id FROM analytics.events.user_sessions WHERE ARRAY_SIZE(events) > 20;

Pitfalls

ARRAY_SIZE returns NULL for NULL inputs, not 0. Use COALESCE(ARRAY_SIZE(col), 0) when NULL and empty should behave identically.
NULL elements are counted toward the length. Use ARRAY_SIZE(ARRAY_COMPACT(col)) to count only non-NULL elements.
For multi-dimensional arrays ARRAY_SIZE reports only the first dimension. Use ARRAY_LENGTH(col, k) or CARDINALITY for inner lengths or total element counts.
ARRAY_SIZE does not validate shape. A ragged ARRAY<ARRAY<T>> returns the number of inner arrays; lengths of those inner arrays require per-element inspection.