ARRAY_MAX

Return the maximum element of an array.

Category: collectionReturns: TDialect: Standard

Syntax

ARRAY_MAX(array)

Description

## Overview Returns the maximum element in the input array using the natural ordering of the element type. ARRAY_MAX is the array-level analogue of the scalar MAX aggregate and is the most direct way to extract a per-row peak from a nested numeric, string, or temporal column without an EXPLODE-and-group rewrite. Typical uses include deriving a per-session peak latency, identifying the latest timestamp in an embedded event trail, and producing a lexicographic upper bound for a per-row tag list. ## Behavior - Returns a scalar value of the array's element type. - NULL elements are ignored; they never compare as larger than any non-NULL value. - For strings, ordering is lexicographic using the engine's default collation. - For numeric types, IEEE 754 semantics apply: NaN, if present and comparable, is treated per the engine's numeric ordering rules. - Works only on comparable element types. Arrays of STRUCT, MAP, or nested ARRAY raise a type error unless all comparable fields align. - Operationally equivalent to aggregate MAX over the output of UNNEST, but runs in a single expression without set-valued intermediate output. ## Null and empty handling - NULL input array returns NULL. - Empty array returns NULL. - Array whose elements are all NULL returns NULL. - Mixed NULL-and-value arrays return the maximum of the non-NULL subset. ## Compatibility - Matches the array/map SQL convention for ARRAY_MAX. Equivalent to REDUCE(array, x, y -> GREATEST(x, y)) for numeric types.

Parameters

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

Examples

-- Max of integers
SELECT ARRAY_MAX(ARRAY[3, 1, 4, 1, 5]);  -- 5

-- Max of strings (lexicographic ordering)
SELECT ARRAY_MAX(ARRAY['banana', 'apple', 'cherry']);  -- 'cherry'

-- NULLs are ignored
SELECT ARRAY_MAX(ARRAY[10, CAST(NULL AS INT), 30, 20]);  -- 30

-- All-NULL array returns NULL
SELECT ARRAY_MAX(CAST(ARRAY(NULL, NULL) AS ARRAY<INT>));  -- NULL

-- Peak timing per session
SELECT session_id, ARRAY_MAX(page_load_ms) AS slowest_page_ms
FROM analytics.events.user_sessions;

-- Rank sessions by longest element in their measurement array
SELECT session_id
FROM analytics.events.user_sessions
ORDER BY ARRAY_MAX(page_load_ms) DESC
LIMIT 10;

Pitfalls

Empty and all-NULL arrays both return NULL. Use COALESCE when a numeric default is required downstream.
String comparison uses the engine's default collation; case and accent folding can change the result. Apply LOWER or a COLLATE clause when case-insensitive ordering is required.
For FLOAT and DOUBLE arrays, NaN handling follows the engine's numeric comparator. Strip NaN explicitly with ARRAY_FILTER if it must never appear as the max.
ARRAY_MAX requires a comparable element type. Arrays of ARRAY, MAP, or structurally-incompatible STRUCT raise a type error; unnest and use scalar MAX instead.