ARRAY_APPEND

Return a new array with one element added at the end.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

ARRAY_APPEND(array, element)

Description

## Overview Returns a new array formed by appending a single element to the end of the input array. ARRAY_APPEND is the immutable counterpart to an in-place push and is the preferred way to extend an array column in a projection without rewriting the entire collection literal. Typical uses include appending a derived tag or sentinel to an existing tag list, terminating an ordered event trail with a synthetic closing event, and adding computed entries to an array produced by a subquery or window frame. ## Behavior - Produces an ARRAY<T> whose length is one greater than the input array's length. - Element order is preserved; the appended value becomes the last element. - The element argument must be type-compatible with the array's element type. Implicit numeric widening follows the usual promotion rules. - Works with nested arrays, structs, and maps as long as the element type matches the container's element type. - Never mutates the input. The original array value is unchanged and the result is a fresh ARRAY. ## Null and empty handling - If the input array is NULL, the function returns NULL. - If the element value is NULL, a NULL element is appended; the result is not NULL. - Appending to an empty array returns a single-element array containing the new element. ## Compatibility - Matches the ARRAY_APPEND semantics defined by standard SQL array/map types. - The non-standard syntax array || element is also supported by the engine and is equivalent in meaning for the single-element case.

Parameters

Name	Type	Description
`array`		Specifies the source array. The result is a new array with the element appended; the input array is never modified in place.
`element`		Specifies the value to append to the end of the array. Must be type-compatible with the array's element type. NULL is allowed and is appended as a NULL element.

Examples

-- Append an integer literal
SELECT ARRAY_APPEND(ARRAY[1, 2, 3], 4);  -- [1, 2, 3, 4]

-- Append a new tag to a per-session tag list
SELECT session_id, ARRAY_APPEND(tags, 'reviewed') AS tags
FROM analytics.events.user_sessions;

-- Appending to a NULL array returns NULL
SELECT ARRAY_APPEND(CAST(NULL AS ARRAY<INT>), 42);  -- NULL

-- Appending NULL adds a NULL element rather than a no-op
SELECT ARRAY_APPEND(ARRAY[1, 2], CAST(NULL AS INT));  -- [1, 2, NULL]

-- Append to an empty array
SELECT ARRAY_APPEND(CAST(ARRAY() AS ARRAY<INT>), 7);  -- [7]

-- Extend a histogram bucket list per group
SELECT customer_id,
       ARRAY_APPEND(ARRAY_AGG(event_type), 'session_closed') AS trail
FROM analytics.events.user_sessions
GROUP BY customer_id;

Pitfalls

ARRAY_APPEND on a NULL array returns NULL, not a single-element array. Wrap inputs in COALESCE(col, ARRAY()) when NULL inputs should degrade to empty arrays.
Appending NULL produces an array with a NULL element; it does not leave the array unchanged. Filter NULL before appending if that behaviour is undesirable.
The element type must match or widen to the array's element type. Appending a string to an array of INT raises a type mismatch rather than silently casting.
ARRAY_APPEND allocates a new array on every call. In loops over large arrays prefer a single ARRAY_AGG or ARRAY() constructor over many incremental appends.