ARRAYS_ZIP

Merge multiple arrays element-wise into an array of structs.

Category: collectionReturns: ARRAY<STRUCT>Dialect: Standard

Syntax

ARRAYS_ZIP(array1, array2, ...)

Description

## Overview Merges two or more arrays element-wise into a single array of anonymous structs, where each struct's fields correspond to the aligned elements of the input arrays. ARRAYS_ZIP is the canonical way to pair up parallel arrays (for example, a timestamp array and a value array) without rebuilding a full relational join. Typical uses include preparing aligned time-series payloads for export, feeding paired positional data to downstream TRANSFORM or EXPLODE operations, and producing struct-typed inputs for functions that expect named fields. ## Behavior - Returns an ARRAY<STRUCT<field_1: T1, field_2: T2, ...>> where each field corresponds positionally to an input array. - Length of the result equals the maximum length of the input arrays. - When inputs have unequal lengths, shorter arrays are padded with NULL to match the longest input. - Field names default to field_1, field_2, ... unless the dialect supports an alias syntax. Use positional access (result[i].field_1) to read values. - Preserves order; element i of input k becomes the k-th field of struct i in the output. - Element types are retained per field; no type unification is performed. ## Null and empty handling - NULL input array (any one) returns NULL for the whole expression. - Empty input arrays produce an empty result array. - NULL elements inside inputs are preserved as NULL fields in the corresponding struct. - Shorter arrays are padded with NULL; the difference between a genuine NULL element and a padding NULL is not recoverable from the output alone. ## Compatibility - Matches the array/map SQL convention for ARRAYS_ZIP with NULL padding of unequal-length inputs. - For equal-length arrays with a user-defined transform, ZIP_WITH is the more general function; ARRAYS_ZIP produces a fixed struct output.

Parameters

Name	Type	Description
`arrays`		Specifies two or more arrays to merge element-wise. Each array contributes one field to the resulting structs. Fields are positional; element types may differ per array.

Examples

-- Zip two arrays
SELECT ARRAYS_ZIP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']);  -- [{1, 'a'}, {2, 'b'}, {3, 'c'}]

-- Zip three arrays into triples
SELECT ARRAYS_ZIP(ARRAY[1, 2], ARRAY['x', 'y'], ARRAY[true, false]);  -- [{1, 'x', true}, {2, 'y', false}]

-- Unequal lengths pad shorter inputs with NULL
SELECT ARRAYS_ZIP(ARRAY[1, 2, 3], ARRAY['a', 'b']);  -- [{1, 'a'}, {2, 'b'}, {3, NULL}]

-- Zip a timestamp array with a value array for a time series
SELECT session_id, ARRAYS_ZIP(event_times, event_values) AS time_series
FROM analytics.events.user_sessions;

-- NULL input returns NULL for the whole expression
SELECT ARRAYS_ZIP(CAST(NULL AS ARRAY<INT>), ARRAY['a', 'b']);  -- NULL

-- Combine with EXPLODE to denormalise paired arrays
SELECT session_id, pair.field_1 AS idx, pair.field_2 AS val
FROM analytics.events.user_sessions
LATERAL VIEW EXPLODE(ARRAYS_ZIP(event_times, event_values)) t AS pair;

Pitfalls

Shorter arrays are padded with NULL, which makes genuine NULL elements and padded positions indistinguishable in the output. Compare lengths up front if that distinction matters.
Field names are positional (field_1, field_2, ...). If you need named fields, wrap the result with TRANSFORM and build named structs explicitly.
A NULL input array collapses the whole result to NULL; it does not produce a same-length array of null-filled structs. Use COALESCE on each input to fall back to empty arrays.
ARRAYS_ZIP allocates a new array of structs. Very wide zip arguments with long inputs can be memory-intensive; prefer a streaming EXPLODE when possible.