ARRAY_MIN

Return the minimum element of an array.

Category: collectionReturns: TDialect: Standard

Syntax

ARRAY_MIN(array)

Description

## Overview Returns the minimum element in the input array using the natural ordering of the element type. ARRAY_MIN is the array-level analogue of the scalar MIN aggregate and is the simplest way to extract a per-row floor from a nested numeric, string, or temporal column without an EXPLODE-and-group rewrite. Typical uses include computing a per-session fastest response, surfacing the earliest timestamp in an embedded event trail, and producing a lexicographic lower 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 smaller than any non-NULL value. - For strings, ordering is lexicographic using the engine's default collation. - For numeric types, IEEE 754 semantics apply; NaN handling follows the engine's numeric comparator. - 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 MIN over the output of UNNEST, but runs in a single expression. ## 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 minimum of the non-NULL subset. ## Compatibility - Matches the array/map SQL convention for ARRAY_MIN. Equivalent to REDUCE(array, x, y -> LEAST(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

-- Min of integers
SELECT ARRAY_MIN(ARRAY[3, 1, 4, 1, 5]);  -- 1

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

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

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

-- Fastest page load per session
SELECT session_id, ARRAY_MIN(page_load_ms) AS fastest_page_ms
FROM analytics.events.user_sessions;

-- Identify sessions whose minimum measurement already exceeds a threshold
SELECT session_id
FROM analytics.events.user_sessions
WHERE ARRAY_MIN(page_load_ms) > 500;

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 COLLATE when case-insensitive ordering is required.
For FLOAT and DOUBLE arrays, NaN handling follows the engine's numeric comparator. Strip NaN with ARRAY_FILTER if it must not participate in the minimum.
ARRAY_MIN requires a comparable element type. Arrays of ARRAY, MAP, or structurally-incompatible STRUCT raise a type error; unnest and use scalar MIN instead.