ARRAY_FIRST

Return the first element of an array.

Category: collectionReturns: TDialect: PostgreSql

Syntax

ARRAY_FIRST(array)

Description

## Overview Returns the element at position 1 of the input array, that is, the first element in the stored order. ARRAY_FIRST is the idiomatic shortcut for reading the head of an array without writing explicit element-at indexing and is especially useful for per-row event trails, ordered tag lists, and any collection whose first element carries domain significance. Typical uses include extracting the earliest event in a per-session trail, reporting the primary tag of a tagged row, and implementing a "head" accessor analogous to the list-head operation in functional languages. ## Behavior - Returns the element at index 1 (array indexing is 1-based). No bounds check is required because empty arrays return NULL. - Element type matches the array's element type, including for STRUCT, MAP, and nested ARRAY elements. - Never mutates the input array. - Does not reorder or sort; the physical first element is returned. If the input was constructed from an unordered source, ARRAY_FIRST is also unordered. - Operationally equivalent to ELEMENT_AT(array, 1). ## Null and empty handling - NULL input array returns NULL. - Empty array returns NULL; no error is raised. - If the first element is itself NULL, NULL is returned. ARRAY_FIRST cannot distinguish "no elements" from "first element is NULL"; pair with ARRAY_SIZE when that distinction matters. ## Compatibility - Matches the standard array/map SQL convention for head access with 1-based indexing. - Equivalent to ELEMENT_AT(array, 1) and to array[1] when the dialect supports bracket indexing.

Parameters

Name	Type	Description
`array`		Specifies the input array. The element at position 1 is returned; no reordering is performed.

Examples

-- First element of a numeric array
SELECT ARRAY_FIRST(ARRAY[10, 20, 30]);  -- 10

-- First event in a per-session trail
SELECT session_id, ARRAY_FIRST(events) AS first_event
FROM analytics.events.user_sessions;

-- First element is NULL
SELECT ARRAY_FIRST(ARRAY[CAST(NULL AS INT), 1, 2]);  -- NULL

-- Empty array returns NULL
SELECT ARRAY_FIRST(CAST(ARRAY() AS ARRAY<INT>));  -- NULL

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

-- Extract funnel entry point per user
SELECT user_id, ARRAY_FIRST(ARRAY_AGG(event_type)) AS entry_event
FROM analytics.events.user_sessions
GROUP BY user_id;

Pitfalls

Array indexing is 1-based here: ARRAY_FIRST maps to position 1, not position 0. Code ported from 0-based languages must adjust.
ARRAY_FIRST cannot distinguish an empty array from an array whose first element is NULL; both return NULL. Check ARRAY_SIZE first when the difference matters.
The function reads the physical first element. If upstream source ordering is non-deterministic, wrap the array with ARRAY_SORT before calling ARRAY_FIRST.
For nested structures the result retains its original element type. Accessing a field of the first struct element requires ARRAY_FIRST(col).field syntax.