Return a new array with one element added at the beginning.
ARRAY_PREPEND(array, element)## Overview Returns a new array formed by placing an element at position 1 and shifting every existing element one position to the right. ARRAY_PREPEND is the immutable counterpart to an in-place head-push and is the preferred way to add a leading sentinel or header to an existing array column. Typical uses include placing a synthetic session-open event at the head of a trail, prepending a default priority tag, and building ordered event histories from a scanned detail table. ## Behavior - Produces an ARRAY<T> whose length is one greater than the input length. - The prepended element becomes position 1; all existing elements shift up by one. - Element type must be compatible with the array's element type. Numeric widening follows standard promotion rules. - Never mutates the input array. - Works with nested arrays, structs, and maps provided the element type matches the container's element type. ## Null and empty handling - NULL input array returns NULL. - NULL element is prepended as a NULL first element; the result is not NULL. - Empty input array produces a single-element array consisting of the new element. ## Compatibility - Matches the array/map SQL convention for ARRAY_PREPEND. The non-standard infix form element || array is accepted as equivalent where available. - Equivalent in outcome to ARRAY_INSERT(array, 1, element) and ARRAY_CAT(ARRAY(element), array).
| Name | Type | Description |
|---|---|---|
array | Specifies the source array. The result is a new array with the element placed before the existing elements; the input array is never modified. | |
element | Specifies the value to place at position 1 of the result. Must be type-compatible with the array's element type. NULL is allowed and is prepended as a NULL element. |
-- Prepend an integer
SELECT ARRAY_PREPEND(ARRAY[2, 3, 4], 1); -- [1, 2, 3, 4]-- Prepend a session-open sentinel to an event trail
SELECT session_id, ARRAY_PREPEND(events, 'session_open') AS events
FROM analytics.events.user_sessions;-- Prepend NULL produces a leading NULL
SELECT ARRAY_PREPEND(ARRAY[1, 2], CAST(NULL AS INT)); -- [NULL, 1, 2]-- Prepend to an empty array
SELECT ARRAY_PREPEND(CAST(ARRAY() AS ARRAY<INT>), 42); -- [42]-- NULL input array returns NULL
SELECT ARRAY_PREPEND(CAST(NULL AS ARRAY<INT>), 1); -- NULL-- Combine with ARRAY_AGG to put a header event before aggregated detail
SELECT user_id, ARRAY_PREPEND(ARRAY_AGG(event_type), 'session_start') AS trail
FROM analytics.events.user_sessions
GROUP BY user_id;