Concatenate two arrays into one.
ARRAY_CAT(array1, array2)## Overview Concatenates two arrays by appending all elements of the second array after all elements of the first, preserving the order of both operands. ARRAY_CAT is the binary form of ARRAY_APPEND and ARRAY_PREPEND and is the preferred way to merge two array-typed expressions without unnesting, aggregating, or building a new literal. Typical uses include stitching together two disjoint event lists into a unified trail, appending a derived sentinel block, and flattening a pair of per-row arrays into a single denormalised column for downstream consumers. ## Behavior - Produces an ARRAY<T> whose length equals the sum of the two input lengths. - Element ordering is deterministic: array1 first, then array2. Duplicates are not removed. - Element types must be compatible. The result type is the common super-type (for example, mixing INT and BIGINT widens to BIGINT). - Concatenating an empty array is a no-op; the other operand is returned as-is. - NULL elements are preserved in both operands and keep their relative positions. - Works with nested arrays (ARRAY<ARRAY<T>>) without flattening. The outer concatenation leaves inner arrays intact. ## Null and empty handling - If either input array is NULL, the function returns NULL. Use COALESCE(col, ARRAY()) to coerce NULL inputs into empty arrays. - Concatenating two empty arrays returns an empty array of the common element type. - NULL elements inside the operands are preserved without any compaction. ## Compatibility - Matches the standard-compatible ARRAY_CAT and the array/map SQL CONCAT-of-arrays convention. - The || operator is accepted as an equivalent infix form where both operands are arrays.
| Name | Type | Description |
|---|---|---|
array1 | Specifies the first (left) array. Its elements appear before those of array2 in the result. | |
array2 | Specifies the second (right) array. Must have the same element type as array1 so both inputs can be merged into a single typed ARRAY result. |
-- Basic concatenation
SELECT ARRAY_CAT(ARRAY[1, 2], ARRAY[3, 4]); -- [1, 2, 3, 4]-- Concatenate a per-session event list with a trailing sentinel array
SELECT session_id, ARRAY_CAT(events, ARRAY['session_closed']) AS trail
FROM analytics.events.user_sessions;-- Concatenating with an empty array preserves the non-empty side
SELECT ARRAY_CAT(ARRAY[1, 2], CAST(ARRAY() AS ARRAY<INT>)); -- [1, 2]-- NULL on either side produces NULL
SELECT ARRAY_CAT(ARRAY[1, 2], CAST(NULL AS ARRAY<INT>)); -- NULL-- NULL elements on either side are preserved in order
SELECT ARRAY_CAT(ARRAY[1, CAST(NULL AS INT)], ARRAY[CAST(NULL AS INT), 4]); -- [1, NULL, NULL, 4]-- Merge events from two detail tables into one row-level history
SELECT u.user_id, ARRAY_CAT(u.click_trail, u.purchase_trail) AS full_trail
FROM analytics.events.user_sessions u;