CARDINALITY

Return the element count of an array or key count of a map.

Category: collectionReturns: INTDialect: Standard

Syntax

CARDINALITY(expr)

Description

## Overview Returns the number of elements in a collection value. For arrays, CARDINALITY returns the total element count (counting all positions across dimensions for multi-dimensional arrays); for maps, it returns the number of key-value pairs. CARDINALITY is the polymorphic length primitive that works across both collection types. Typical uses include a single function call that counts elements regardless of whether the column is an ARRAY or a MAP, per-row length metrics for nested collections, and gating downstream work by minimum size thresholds. ## Behavior - Returns an INT equal to the element count. - For arrays, counts every element including NULL elements and recursively across dimensions for multi-dimensional arrays. - For maps, counts every distinct key (equivalent to ARRAY_SIZE(MAP_KEYS(m))). - Runs in O(1) for simple arrays and maps where the length is stored in metadata. - Returns NULL when the input is NULL. - Does not distinguish between an empty collection (returns 0) and a NULL collection (returns NULL). ## Null and empty handling - NULL input returns NULL. - Empty array returns 0. - Empty map returns 0. - NULL elements are counted toward array cardinality. - Duplicate keys in a map cannot occur; every key contributes exactly one. ## Compatibility - Matches the SQL:2016 CARDINALITY function. SIZE and ARRAY_SIZE are common synonyms for the array case. - For multi-dimensional arrays this engine returns the total element count. If you need only the first-dimension length, use ARRAY_SIZE or ARRAY_LENGTH with an explicit dimension.

Parameters

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

Examples

-- Count of a flat array
SELECT CARDINALITY(ARRAY[1, 2, 3]);  -- 3

-- Count of a map's key-value pairs
SELECT CARDINALITY(MAP('a', 1, 'b', 2));  -- 2

-- Per-session trail length
SELECT session_id, CARDINALITY(events) AS event_count
FROM analytics.events.user_sessions;

-- Total element count for a nested array (across dimensions)
SELECT CARDINALITY(ARRAY[[1, 2], [3, 4]]);  -- 4

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

-- Empty collection returns 0
SELECT CARDINALITY(CAST(ARRAY() AS ARRAY<INT>)) AS empty_arr,
       CARDINALITY(CAST(MAP() AS MAP<STRING, INT>)) AS empty_map;

Pitfalls

CARDINALITY on a NULL input returns NULL, not 0. Use COALESCE(CARDINALITY(col), 0) when callers expect NULL to behave like empty.
For multi-dimensional arrays, CARDINALITY returns the total element count, unlike ARRAY_SIZE which returns only the outer dimension's length. Choose the function that matches the intent.
NULL elements count toward array cardinality. For non-NULL-only counts, use CARDINALITY(ARRAY_COMPACT(col)).
Map cardinality equals the number of keys. Duplicate keys are impossible, so cardinality also equals the number of values.