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.

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                    |
+-------------------------+