Categories:

Semi-structured and structured data functions (Array/Object)

ARRAY_MIN

Given an input ARRAY, returns the element with the lowest value that is not a SQL NULL. If the input ARRAY is empty or contains only SQL NULL elements, this function returns NULL.

Syntax

ARRAY_MIN( <array> )
Copy

Arguments

array

The input ARRAY.

Returns

This function returns a VARIANT that contains the element with the lowest value that is not a SQL NULL.

The function returns NULL if array is NULL, empty, or contains only SQL NULL elements.

Usage notes

  • A SQL NULL is distinct from an explicit null value in semi-structured data (for example, a JSON null in JSON data). Explicit null values are considered when identifying the element with the lowest value.

  • The function determines the element to return by comparing the elements in the array. The function supports comparing elements of the same data type or of the following data types:

    • Elements of the NUMBER and FLOAT data types.

    • Elements of the TIMESTAMP_LTZ and TIMESTAMP_TZ data types.

    If the array contains elements of other data types, cast the elements to a common data type, as shown in the example below.

Examples

The following example returns a VARIANT containing the element with the lowest value in an ARRAY constant:

SELECT ARRAY_MIN([20, 0, NULL, 10, NULL]);
Copy
+------------------------------------+ | ARRAY_MIN([20, 0, NULL, 10, NULL]) | |------------------------------------| | 0 | +------------------------------------+

The following example demonstrates that the function returns NULL if the input ARRAY is empty:

SELECT ARRAY_MIN([]);
Copy
+---------------+ | ARRAY_MIN([]) | |---------------| | NULL | +---------------+

The following example demonstrates that the function returns NULL if the input ARRAY contains only SQL NULLs:

SELECT ARRAY_MIN([NULL, NULL, NULL]);
Copy
+-------------------------+ | ARRAY_MIN([NULL, NULL]) | |-------------------------| | NULL | +-------------------------+

To determine the minimum value in an array with elements of different data types, cast the elements to the same data type. The following example casts a DATE element to a TIMESTAMP element to determine the minimum value in the array:

SELECT ARRAY_MIN([date1::TIMESTAMP, timestamp1]) AS array_min FROM ( VALUES ('1999-01-01'::DATE, '2023-12-09 22:09:26.000000000'::TIMESTAMP), ('2023-12-09'::DATE, '1999-01-01 22:09:26.000000000'::TIMESTAMP) AS t(date1, timestamp1) );
Copy
+---------------------------+ | ARRAY_MIN | |---------------------------| | "1999-01-01 00:00:00.000" | | "1999-01-01 22:09:26.000" | +---------------------------+
Language: English