- Categories:
Semi-structured Data Functions (Array/Object)
ARRAY_MAX¶
Given an input ARRAY, returns the element with the highest 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_MAX( <array> )
Arguments¶
array
The input ARRAY.
Returns¶
This function returns a VARIANT that contains the element with the highest 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 highest value.
Examples¶
The following example returns a VARIANT containing the element with the highest value in an ARRAY constant:
SELECT ARRAY_MAX([20, 0, NULL, 10, NULL]);
+------------------------------------+
| ARRAY_MAX([20, 0, NULL, 10, NULL]) |
|------------------------------------|
| 20 |
+------------------------------------+
The following example demonstrates that a JSON null is handled differently than a SQL NULL. If array
contains a JSON
null, the function returns the JSON null.
SELECT ARRAY_MAX([NULL, PARSE_JSON('null'), NULL]);
+--------------------------------------------------+
| ARRAY_MAX([20, 0, PARSE_JSON('NULL'), 10, NULL]) |
|--------------------------------------------------|
| null |
+--------------------------------------------------+
The following example demonstrates that the function returns NULL if the input ARRAY is empty:
SELECT ARRAY_MAX([]);
+---------------+
| ARRAY_MAX([]) |
|---------------|
| NULL |
+---------------+
The following example demonstrates that the function returns NULL if the input ARRAY contains only SQL NULLs:
SELECT ARRAY_MAX([NULL, NULL, NULL]);
+-------------------------+
| ARRAY_MAX([NULL, NULL]) |
|-------------------------|
| NULL |
+-------------------------+