ARRAY_REVERSE

Return a new array with the elements in reverse order.

Category: collectionReturns: ARRAY<T>Dialect: PostgreSql

Syntax

ARRAY_REVERSE(array)

Description

## Overview Returns a new array whose elements appear in the reverse of their input order. ARRAY_REVERSE is the idiomatic way to flip an ordered array column without involving EXPLODE and ROW_NUMBER. It pairs naturally with ARRAY_FIRST and ARRAY_LAST when code must swap head and tail semantics. Typical uses include presenting an event trail in most-recent-first order, reversing a priority-ordered tag list for display, and undoing a prior sort without reapplying ARRAY_SORT. ## Behavior - Produces an ARRAY<T> of the same length and element type as the input. - Each position i in the result contains the element at position (ARRAY_SIZE(input) - i + 1) in the input. - NULL elements are preserved and are reversed along with other elements. - Runs in O(n). The input is never mutated. - Works on any element type, including nested arrays, structs, and maps. - Does not sort; existing ordering is simply mirrored. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array. - Single-element input returns an identical single-element array. - NULL elements keep their positions mirrored but are not dropped or coalesced. ## Compatibility - Matches the array/map SQL convention for ARRAY_REVERSE. Equivalent to TRANSFORM(SEQUENCE(1, ARRAY_SIZE(array)), i -> array[ARRAY_SIZE(array) - i + 1]), without lambda overhead.

Parameters

Name	Type	Description
`array`		Specifies the input array. The result is a new array whose elements appear in the reverse of their input order.

Examples

-- Reverse an integer array
SELECT ARRAY_REVERSE(ARRAY[1, 2, 3, 4]);  -- [4, 3, 2, 1]

-- Reverse a per-session event trail so the most recent event is first
SELECT session_id, ARRAY_REVERSE(events) AS events_desc
FROM analytics.events.user_sessions;

-- NULL positions are reversed along with other elements
SELECT ARRAY_REVERSE(ARRAY[1, CAST(NULL AS INT), 3]);  -- [3, NULL, 1]

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

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

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

Pitfalls

ARRAY_REVERSE does not sort. If the input is unsorted, the reversed result is also unsorted; apply ARRAY_SORT first if descending order is required.
NULL elements are preserved and reversed rather than coalesced. Combine with ARRAY_COMPACT when downstream consumers cannot handle NULLs.
The operation is O(n) and allocates a new array of the same size. For very large arrays consider whether a lazy reverse iteration (EXPLODE plus ORDER BY DESC) fits better.
Reversing a multi-dimensional array only reverses the outermost dimension. Inner arrays keep their original order; apply TRANSFORM(ARRAY_REVERSE, inner) if full reversal is required.