Return every 1-based position where a value appears in an array.
ARRAY_POSITIONS(array, element)## Overview Returns an array of all 1-based positions where the target element appears in the input array, in ascending order. ARRAY_POSITIONS is the plural sibling of ARRAY_POSITION and is the preferred way to enumerate every occurrence of a value without unnesting. Typical uses include locating every instance of a repeated event in a trail, deriving inter-occurrence gaps for rate analyses, and building dense position vectors for subsequent index-based transforms. ## Behavior - Returns an ARRAY<INT> whose elements are the positions of every match, sorted ascending. - Returns an empty array (not NULL) when no occurrences are found. - Element comparison is structural and uses SQL equality. - Searching for NULL returns the positions of every NULL element. - Works on any comparable element type. - Scanning is linear in the array size. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array (no occurrences). - If the target is NULL and the array contains NULL elements, their positions are returned. If no NULL is present, an empty array is returned. - Arrays of a single NULL with a NULL target return [1]. ## Compatibility - Matches the standard-compatible ARRAY_POSITIONS convention. Where dialects disagree about NULL equality, this engine treats NULL as equal to NULL for enumeration purposes. - Equivalent in outcome to TRANSFORM(FILTER(SEQUENCE(1, ARRAY_SIZE(array)), i -> array[i] = element), i -> i), but much more efficient.
| Name | Type | Description |
|---|---|---|
array | Specifies the input array to search. | |
element | Specifies the value whose positions are collected. Must be type-compatible with the array's element type. |
-- Multiple occurrences
SELECT ARRAY_POSITIONS(ARRAY[1, 2, 1, 3, 1], 1); -- [1, 3, 5]-- Single occurrence
SELECT ARRAY_POSITIONS(ARRAY['a', 'b', 'c'], 'b'); -- [2]-- No occurrences yields empty array
SELECT ARRAY_POSITIONS(ARRAY[1, 2, 3], 5); -- []-- Find NULL positions
SELECT ARRAY_POSITIONS(ARRAY[1, CAST(NULL AS INT), 3, CAST(NULL AS INT)], CAST(NULL AS INT)); -- [2, 4]-- Find every occurrence of an event type in a per-session trail
SELECT session_id, ARRAY_POSITIONS(events, 'click') AS click_indexes
FROM analytics.events.user_sessions;-- Measure inter-occurrence gaps for a recurring event
SELECT session_id,
ARRAY_POSITIONS(events, 'page_view') AS view_indexes,
ARRAY_SIZE(ARRAY_POSITIONS(events, 'page_view')) AS view_count
FROM analytics.events.user_sessions;