Generate the set of valid 1-based subscripts along an array dimension.
GENERATE_SUBSCRIPTS(array, dim)## Overview Generates a set of INT values covering every valid subscript of the input array along the specified dimension. GENERATE_SUBSCRIPTS is a set-returning function typically used in FROM clauses or lateral joins to iterate over array positions with positional context (for example, to pair each element with its index). Typical uses include joining a per-row array to a positional index column, producing position-aware transformations that EXPLODE cannot express directly, and iterating over the outer dimension of a multi-dimensional array. ## Behavior - Produces a set of INT rows, one per valid subscript from ARRAY_LOWER(array, dim) through ARRAY_UPPER(array, dim). - Subscripts are ascending and 1-based. - NULL input array produces an empty set (no rows). - Empty input array produces an empty set. - Works in FROM or LATERAL contexts; behaves like a table-valued function. - Does not enumerate element values; pair with ELEMENT_AT or bracket indexing to access the array at each generated subscript. ## Null and empty handling - NULL input array produces zero rows (no NULL row is emitted). - Empty input array produces zero rows. - Dimension beyond the array's rank produces zero rows. - NULL elements inside the array do not affect the generated subscripts. ## Compatibility - Matches the standard-compatible GENERATE_SUBSCRIPTS convention for 1-based arrays. - For simple 1D arrays, GENERATE_SUBSCRIPTS(arr, 1) is equivalent to SEQUENCE(1, ARRAY_SIZE(arr)) and LATERAL UNNEST with a positional companion column.
| Name | Type | Description |
|---|---|---|
array | Specifies the input array whose subscripts are generated. | |
dim | Specifies the 1-based dimension along which subscripts are generated. |
-- Subscripts for a 3-element array
SELECT GENERATE_SUBSCRIPTS(ARRAY[10, 20, 30], 1); -- 1, 2, 3-- Iterate with ELEMENT_AT
SELECT i, ELEMENT_AT(ARRAY['a', 'b', 'c'], i) AS val
FROM GENERATE_SUBSCRIPTS(ARRAY['a', 'b', 'c'], 1) AS t(i);-- First dimension of a 2D array
SELECT GENERATE_SUBSCRIPTS(ARRAY[[1, 2], [3, 4]], 1); -- 1, 2-- Pair a per-session event trail with positional indexes
SELECT s.session_id, i.idx, ELEMENT_AT(s.events, i.idx) AS event
FROM analytics.events.user_sessions s,
GENERATE_SUBSCRIPTS(s.events, 1) AS i(idx);-- NULL input produces an empty set
SELECT idx FROM GENERATE_SUBSCRIPTS(CAST(NULL AS ARRAY<INT>), 1) AS t(idx);-- Empty input array also produces an empty set
SELECT idx FROM GENERATE_SUBSCRIPTS(CAST(ARRAY() AS ARRAY<INT>), 1) AS t(idx);