ARRAY_LENGTH

Return the length of a given array dimension.

Category: collectionReturns: INTDialect: PostgreSql

Syntax

ARRAY_LENGTH(array, dim)

Description

## Overview Returns the count of elements along a specified dimension of an array. ARRAY_LENGTH is the dimension-aware counterpart of ARRAY_SIZE and CARDINALITY, and it is the preferred function when your arrays are multi-dimensional or when you want to express the dimension argument explicitly in portable code. Typical uses include deriving a per-row count metric for a nested array column, validating that multi-dimensional arrays conform to an expected rank, and distinguishing empty arrays from NULL arrays in reporting. ## Behavior - Returns an INT equal to the number of elements along the requested dimension. - Dimensions are 1-based. Pass 1 for the outermost dimension of a flat array. - For a multi-dimensional rectangular array, higher dimensions measure the inner axes in row-major order. - Requesting a dimension beyond the array's rank returns NULL, not an error. - Does not copy or materialise the array; it inspects metadata only. ## Null and empty handling - NULL input array returns NULL. - Empty array returns NULL for every dimension (zero-sized arrays have no extent to report). - Non-existent dimension returns NULL rather than raising. - NULL elements within the array are counted as elements; they do not affect the length. ## Compatibility - Matches the standard-compatible ARRAY_LENGTH with an explicit dimension argument. - For a simple count of elements in a one-dimensional array, ARRAY_SIZE or CARDINALITY are more concise. Prefer ARRAY_LENGTH when multiple dimensions are involved or when code must be explicit.

Parameters

Name	Type	Description
`array`		Specifies the input array whose dimension length is measured.
`dim`		Specifies which dimension to measure, using 1-based indexing. For a one-dimensional array, pass 1. For a multi-dimensional rectangular array, pass 2 or higher to measure inner dimensions.

Examples

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

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

-- Second dimension of a 2D array
SELECT ARRAY_LENGTH(ARRAY[[1, 2, 3], [4, 5, 6]], 2);  -- 3

-- First dimension of a 2D array
SELECT ARRAY_LENGTH(ARRAY[[1, 2], [3, 4]], 1);  -- 2

-- NULL array returns NULL
SELECT ARRAY_LENGTH(CAST(NULL AS ARRAY<INT>), 1);  -- NULL

-- Empty array returns NULL (no extent)
SELECT ARRAY_LENGTH(CAST(ARRAY() AS ARRAY<INT>), 1);  -- NULL

Pitfalls

ARRAY_LENGTH on an empty array returns NULL, not 0. Use ARRAY_SIZE or CARDINALITY when a zero count is required.
Dimensions are 1-based. Passing 0 returns NULL rather than raising; passing a dimension higher than the array's rank also returns NULL.
Multi-dimensional support assumes rectangular shape. Ragged ARRAY<ARRAY<T>> values report the outermost length only; inner dimensions must be measured per inner array.
ARRAY_LENGTH does not count non-NULL elements. Use SIZE(ARRAY_COMPACT(col), 1) or an element-wise filter when NULLs must be excluded.