ARRAY_SORT

Return a new array with the elements sorted in ascending order.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

ARRAY_SORT(array)

Description

## Overview Returns a new array containing the input elements in ascending order using the natural ordering of the element type. ARRAY_SORT is the default sorter for array-valued expressions and is the preferred way to impose a deterministic element order before slicing, comparing, or rendering. Typical uses include preparing a per-row array for percentile or rank extraction, normalising a tag list before a hash-based lookup key, and producing a stable sort input for downstream vector operations. ## Behavior - Returns an ARRAY<T> of the same length and element type as the input. - Ordering is ascending by the element type's natural order. Strings use the engine's default collation; numeric types follow IEEE 754 semantics. - NULL elements are placed at the end of the result (NULLS LAST). - Sort is stable for equal elements; their relative input order is preserved. - Runs in O(n log n). The input is never mutated. - Works only on comparable element types. Arrays of STRUCT, MAP, or nested ARRAY raise a type error unless all comparable fields align. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array. - NULL elements are preserved and placed after all non-NULL elements. - For all-NULL arrays, the result is an array of the same length containing only NULLs. ## Compatibility - Matches the array/map SQL convention for ARRAY_SORT with ascending order and NULLS LAST. - For descending order, use SORT_ARRAY(array, false) or ARRAY_REVERSE(ARRAY_SORT(array)). For custom orderings, use SORT_ARRAY with an explicit comparator where supported.

Parameters

Name	Type	Description
`array`		Specifies the input array. Elements must be of a comparable type (numeric, string, date, timestamp, boolean).

Examples

-- Sort integers
SELECT ARRAY_SORT(ARRAY[3, 1, 4, 1, 5]);  -- [1, 1, 3, 4, 5]

-- Sort a per-session latency array for percentile analysis
SELECT session_id, ARRAY_SORT(page_load_ms) AS latencies_sorted
FROM analytics.events.user_sessions;

-- NULLs are placed at the end (NULLS LAST)
SELECT ARRAY_SORT(ARRAY[3, CAST(NULL AS INT), 1, 2]);  -- [1, 2, 3, NULL]

-- Already sorted array is returned in the same order
SELECT ARRAY_SORT(ARRAY[1, 2, 3]);  -- [1, 2, 3]

-- Build a stable distinct-sorted set per group
SELECT user_id, ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(event_type))) AS sorted_events
FROM analytics.events.user_sessions
GROUP BY user_id;

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

Pitfalls

NULLs are placed at the end, not the beginning. If callers expect NULLS FIRST, use SORT_ARRAY or pre-filter NULLs with ARRAY_COMPACT before sorting.
String comparison uses the engine's default collation. For case-insensitive ordering wrap with LOWER or apply a COLLATE clause.
ARRAY_SORT is stable for equal keys, but the engine does not guarantee stability across optimiser rewrites. Do not depend on input order for tie-breaking.
The function requires a comparable element type. For ARRAY<STRUCT> sort by a field, explode and reaggregate with ORDER BY rather than calling ARRAY_SORT.
Sort is O(n log n). For extremely large per-row arrays prefer pushing sort logic to a relational step with EXPLODE and ORDER BY.