SIZE

Return the element count of an array or map.

Category: collectionReturns: INTDialect: Standard

Syntax

SIZE(expr)

Description

## Overview Returns the element count of an ARRAY or the key count of a MAP. SIZE is the polymorphic length primitive alongside CARDINALITY and ARRAY_SIZE, and it is the most commonly used function when the collection type may be either an array or a map. Typical uses include per-row size metrics for nested collections, gating downstream work by a minimum size threshold, and distinguishing empty (length 0) from NULL (length NULL) collection columns. ## Behavior - Returns an INT equal to the element count of the collection. - For arrays, counts every element including NULL elements. - For maps, counts every key-value pair. - Runs in O(1) because the size is carried in metadata. - Returns NULL when the input is NULL. - For multi-dimensional arrays, counts only the outer dimension's elements in this engine. Use CARDINALITY or ARRAY_LENGTH with an explicit dimension for deeper counts. ## Null and empty handling - NULL input returns NULL. - Empty array or empty map returns 0. - NULL elements are counted toward array size. - For maps, every key counts exactly once because maps cannot have duplicate keys. ## Compatibility - Matches the array/map SQL convention for SIZE. CARDINALITY and ARRAY_SIZE are common synonyms for the array case. - Code that must treat NULL identically to empty should wrap with COALESCE(SIZE(col), 0).

Parameters

Name	Type	Description
`expr`		Specifies the collection whose size is returned. Accepts an ARRAY or MAP value.

Examples

-- Size of an array
SELECT SIZE(ARRAY[1, 2, 3]);  -- 3

-- Size of a map
SELECT SIZE(MAP('a', 1, 'b', 2));  -- 2

-- Per-session event count
SELECT session_id, SIZE(events) AS event_count
FROM analytics.events.user_sessions;

-- Empty collection returns 0
SELECT SIZE(CAST(ARRAY() AS ARRAY<INT>));  -- 0

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

-- Gate on both arrays and maps in the same projection
SELECT session_id
FROM analytics.events.user_sessions
WHERE SIZE(events) >= 3 AND SIZE(attributes) >= 1;

Pitfalls

SIZE returns NULL for NULL inputs, not 0. Use COALESCE(SIZE(col), 0) when NULL and empty must be treated identically.
NULL array elements count toward the length. Use SIZE(ARRAY_COMPACT(col)) to count only non-NULL elements.
For multi-dimensional arrays SIZE reports only the outer dimension. Use CARDINALITY for the total count or ARRAY_LENGTH with an explicit dimension.
SIZE and CARDINALITY are usually synonyms, but they can diverge on multi-dimensional arrays. Pick one and stick with it in the same project to avoid confusion.