ARRAY_SHUFFLE

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

Category: collectionReturns: ARRAY<T>Dialect: PostgreSql

Syntax

ARRAY_SHUFFLE(array)

Description

## Overview Returns a new array containing every element of the input in a uniformly random order. ARRAY_SHUFFLE is the array-level equivalent of a row-level random permutation and is useful for randomising per-row ordered collections, such as shuffling an array of candidate items for A/B rotation or producing randomised test fixtures. This function is the standard-compatible name. See SHUFFLE for the standard-dialect synonym with identical semantics. Typical uses include scrambling a per-row event trail for load generation, randomising a tag list to avoid ordering bias in downstream consumers, and preparing a shuffle-then-slice pipeline for sampling 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 in the input appear the same number of times in the output. - 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 are shuffled like any other element. - Single-element arrays are returned as-is (there is only one permutation). ## Compatibility - Matches the array/map SQL convention for ARRAY_SHUFFLE. SHUFFLE is the standard-dialect 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 ARRAY_SHUFFLE(ARRAY[1, 2, 3, 4, 5]);  -- e.g. [3, 5, 1, 4, 2]

-- Randomise a per-session event trail for load generation
SELECT session_id, ARRAY_SHUFFLE(events) AS shuffled
FROM analytics.events.user_sessions;

-- NULL elements are kept, just in a shuffled position
SELECT ARRAY_SHUFFLE(ARRAY[1, CAST(NULL AS INT), 3]);  -- e.g. [3, 1, NULL]

-- Single-element array is unchanged
SELECT ARRAY_SHUFFLE(ARRAY[42]);  -- [42]

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

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

Pitfalls

Results are non-deterministic. Use a deterministic hash over a row identifier plus position if you need reproducible shuffles per row.
ARRAY_SHUFFLE preserves duplicates. If you want a deduplicated random sample, compose ARRAY_SHUFFLE with ARRAY_DISTINCT.
NULL elements participate in the shuffle. Filter with ARRAY_COMPACT first if NULLs must never appear at specific positions.
For a random subset of size n, prefer ARRAY_SAMPLE(array, n) or SLICE(ARRAY_SHUFFLE(array), 1, n); shuffling a large array just to drop most elements is wasteful.