ARRAY_SORT function

Creates a sorted version of the input array in either ascending or descending order. You can specify where NULL values should appear in the result. The function is NULL-safe, meaning it treats NULLs are treated as known objects.

Syntax

ARRAY_SORT( array [, sort_ascending [, nulls_first]] )

Arguments

array

A SUPER expression that specifies the array to be sorted.

sort_ascending

A boolean value that specifies whether to sort the array in ascending or descending order:

• Specify TRUE to sort the elements in ascending order.

• Specify FALSE to sort the elements in descending order.

The default is TRUE.

nulls_first

A boolean value that specifies the NULL positioning:

• Specify TRUE to place NULLs at the beginning of the sorted array.

• Specify FALSE to place NULLs at the end of the sorted array.

Return type

The ARRAY_SORT function returns a SUPER type.

Note

When sorting arrays containing mixed data types, elements are ordered according to the following type precedence:

• Boolean values

• Numeric values

• String values

• Arrays

• Objects/Dictionaries

Within each type category, elements are sorted according to their natural ordering (e.g., numbers are sorted numerically, strings alphabetically).

Example

The following examples show the ARRAY_SORT function.

-- Ascending order (default)
SELECT ARRAY_SORT(ARRAY('b', 'a', 0, NULL, 1, false));
        array_sort        
--------------------------
 [false,0,1,"a","b",null]
(1 row)

-- Descending order
SELECT ARRAY_SORT(ARRAY('b', 'a', 0, NULL, 1, false), False);
        array_sort        
--------------------------
 [null,"b","a",1,0,false]
(1 row)

-- Descending order with NULLs at the end of the sorted array
SELECT ARRAY_SORT(ARRAY('b', 'a', 0, NULL, 1, false), False, False);
        array_sort        
--------------------------
 ["b","a",1,0,false,null]
(1 row)

See also

• ARRAY_DISTINCT function

• ARRAY_FLATTEN function

• SUBARRAY function