FLATTEN

Flatten a nested array by one level, concatenating inner arrays into a single flat array.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

FLATTEN(array)

Description

## Overview Flattens a nested array by exactly one level, concatenating all inner arrays into a single array in the outer's iteration order. FLATTEN is the canonical operator for turning an ARRAY<ARRAY<T>> into an ARRAY<T> and is the preferred way to collapse aggregated arrays without unnesting and re-aggregating. Typical uses include collapsing per-row arrays aggregated with ARRAY_AGG(arr) into a single per-group array, merging nested event lists gathered from multiple sources, and preparing flat element sequences for downstream scalar transforms. ## Behavior - Returns ARRAY<T>, where T is the element type of the inner arrays. - Concatenates inner arrays in the order they appear in the outer array. - Only one level of nesting is removed per call. For ARRAY<ARRAY<ARRAY<T>>> callers must apply FLATTEN twice. - Empty inner arrays contribute nothing and are effectively skipped. - NULL inner arrays propagate: if any inner array is NULL, the result is NULL (treat-as-fatal NULL semantics). - The output length equals the sum of the inner array lengths. ## Null and empty handling - NULL outer array returns NULL. - NULL inner array (any element of the outer) returns NULL for the whole result. - Empty outer array returns an empty array of type ARRAY<T>. - Empty inner arrays are skipped without error. - NULL elements within inner arrays are preserved. ## Compatibility - Matches the array/map SQL convention for FLATTEN of a single nesting level. - Equivalent to CONCAT applied across all inner arrays, but without enumerating them explicitly.

Parameters

Name	Type	Description
`array`		Specifies a nested array (an array of arrays) to flatten. Only one level of nesting is removed per call.

Examples

-- Flatten a basic nested integer array
SELECT FLATTEN(ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);  -- [1, 2, 3, 4]

-- Uneven inner arrays
SELECT FLATTEN(ARRAY[ARRAY[1], ARRAY[2, 3], ARRAY[4, 5, 6]]);  -- [1, 2, 3, 4, 5, 6]

-- Empty inner arrays are omitted
SELECT FLATTEN(ARRAY[ARRAY[1, 2], CAST(ARRAY() AS ARRAY<INT>), ARRAY[3]]);  -- [1, 2, 3]

-- Only one level of nesting is removed per call
SELECT FLATTEN(ARRAY[ARRAY[ARRAY[1, 2]], ARRAY[ARRAY[3, 4]]]);  -- [[1, 2], [3, 4]]

-- Flatten arrays of per-session tag arrays rolled up per user
SELECT user_id, FLATTEN(ARRAY_AGG(tags)) AS all_tags
FROM analytics.events.user_sessions
GROUP BY user_id;

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

Pitfalls

FLATTEN removes exactly one level of nesting. For deep nests, apply FLATTEN repeatedly or combine with TRANSFORM.
A single NULL inner array propagates NULL to the whole result. Coerce NULL inner arrays to empty arrays with TRANSFORM(outer, a -> COALESCE(a, ARRAY())) before flattening.
Empty inner arrays silently contribute no elements. If you need to track how many inner arrays were empty, compute SIZE on each before flattening.
Result size can be much larger than the input's outer length. For very large per-row nested arrays consider streaming via EXPLODE rather than materialising the flattened array.