ARRAY_POSITION

Return the 1-based position of the first matching element in an array.

Category: collectionReturns: INTDialect: Standard

Syntax

ARRAY_POSITION(array, element)

Description

## Overview Returns the 1-based index of the first occurrence of the target element in the array, or NULL when the element is not found. ARRAY_POSITION is the canonical locator for array-typed columns and is the building block for ordered-event detection, funnel step indexing, and lookup-by-value workflows. Typical uses include finding where a key event first appears in a per-session trail, reporting the rank of a tag in a priority-ordered tag list, and computing differences between first-occurrence indexes across two arrays. ## Behavior - Returns an INT position in the range 1 to ARRAY_SIZE(array) or NULL when no match is found. - Scanning is left-to-right with early termination on the first match. - Element comparison is structural and uses SQL equality. - Searching for NULL matches the first NULL element (in this function NULL is treated as equal to NULL). - Works on any comparable element type, including nested STRUCT and ARRAY elements. - Does not mutate the input. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns NULL (no position exists). - If the target is NULL and the array contains a NULL, the position of the first NULL is returned. - If the target is NULL and the array has no NULL, NULL is returned. - If the target is non-NULL and the array is all NULL, NULL is returned. ## Compatibility - Matches the standard SQL ARRAY_POSITION convention with 1-based indexing. Engines that expose a similar primitive may diverge on NULL-matching semantics; this engine treats NULL as equal to NULL for locator purposes. - Use ARRAY_POSITIONS to retrieve every matching position rather than just the first.

Parameters

Name	Type	Description
`array`		Specifies the input array to search.
`element`		Specifies the value to locate. Must be type-compatible with the array's element type.

Examples

-- Locate an element
SELECT ARRAY_POSITION(ARRAY['a', 'b', 'c', 'd'], 'c');  -- 3

-- Element not found returns NULL
SELECT ARRAY_POSITION(ARRAY[1, 2, 3], 5);  -- NULL

-- First occurrence wins when duplicates exist
SELECT ARRAY_POSITION(ARRAY[10, 20, 10, 30], 10);  -- 1

-- Find NULL in an array
SELECT ARRAY_POSITION(ARRAY[1, CAST(NULL AS INT), 3], CAST(NULL AS INT));  -- 2

-- Locate the entry event within a per-session trail
SELECT session_id, ARRAY_POSITION(events, 'checkout_start') AS checkout_index
FROM analytics.events.user_sessions;

-- Partition sessions by whether a key event appears before index 5
SELECT session_id,
       CASE WHEN COALESCE(ARRAY_POSITION(events, 'add_to_cart'), 99) < 5 THEN 'fast' ELSE 'slow' END AS cart_speed
FROM analytics.events.user_sessions;

Pitfalls

Indexes are 1-based. ARRAY_POSITION(arr, first_element) returns 1, not 0; code ported from 0-based languages must adjust arithmetic.
Returns NULL when the element is absent, not 0 or -1. Wrap with COALESCE if callers expect a numeric sentinel.
NULL matches NULL in this function, which differs from ARRAY_CONTAINS three-valued logic. Plan for this when searching sparse arrays.
Only the first occurrence is returned. For all positions use ARRAY_POSITIONS; for a membership test use ARRAY_CONTAINS.
Search is linear per row. For very large arrays or high-selectivity filters, consider an EXPLODE-and-join or bitmap design.