SHUFFLE

Return a new array with the elements in a random order.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

SHUFFLE(array)

Description

## Overview Returns a new array containing every element of the input in a uniformly random order. SHUFFLE is the standard-dialect name for the function (ARRAY_SHUFFLE is the standard-compatible synonym) and is the preferred randomiser for per-row collections. Typical uses include scrambling per-row event trails for load generation or A/B testing, randomising tag order prior to position-sensitive downstream code, and producing a shuffle-then-slice pipeline for inline sampling of a prefix. ## Behavior - Returns an ARRAY<T> of the same length and element type as the input. - Every input element appears exactly once in the output; duplicates are preserved. - Ordering is non-deterministic across invocations unless a session-level random seed is fixed. - Runs in O(n). The input is never mutated. - Works on any element type, including nested arrays, structs, and maps. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array. - NULL elements in the input are preserved and shuffled along with other elements. - Single-element arrays are returned unchanged. ## Compatibility - Matches the array/map SQL convention for SHUFFLE. ARRAY_SHUFFLE is the standard-compatible synonym. - Reproducibility requires a fixed random seed; without one, two invocations may differ even on the same input row.

Parameters

Name	Type	Description
`array`		Specifies the input array. Every element appears in the output exactly once, in a random order.

Examples

-- Shuffle a numeric array
SELECT SHUFFLE(ARRAY[1, 2, 3, 4, 5]);  -- e.g. [4, 1, 5, 2, 3]

-- Randomise per-session events
SELECT session_id, SHUFFLE(events) AS shuffled
FROM analytics.events.user_sessions;

-- NULL elements are preserved in the shuffle
SELECT SHUFFLE(ARRAY[1, CAST(NULL AS INT), 3]);  -- e.g. [NULL, 3, 1]

-- Single-element array is returned as-is
SELECT SHUFFLE(ARRAY[42]);  -- [42]

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

-- Empty array returns an empty array
SELECT SHUFFLE(CAST(ARRAY() AS ARRAY<INT>));  -- []

Pitfalls

Results are non-deterministic. Use a deterministic hash over a row identifier plus position when reproducible shuffles per row are required.
SHUFFLE preserves duplicates. Combine with ARRAY_DISTINCT if you need a shuffled set.
NULL elements participate in the shuffle. Filter with ARRAY_COMPACT first if NULLs must not land at specific positions.
For a random subset of size n, prefer ARRAY_SAMPLE(array, n) or SLICE(SHUFFLE(array), 1, n); shuffling a large array just to drop most elements is wasteful.