ARRAY_SORT

​

Syntax

​

Scalar

array_sort(array [, comparator])

​

Arguments

• T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY or STRUCT.

​

Returns

• Type: ARRAY<T>
• Possible values: The sorted input elements.
• NULL behavior: NULL array returns NULL. Natural ordering retains NULL elements last; comparator sees NULL elements and must supply their ordering.
• Shape: Same element count and type as input.
• Ordering: Natural ascending with NULLs last, or comparator-defined order.

​

Properties

​

Usage notes

• Floating-point sorting distinguishes positive and negative zero and different NaN values. STRING and BINARY values use lexicographic order. Nested arrays and structs are sorted by their elements or fields.
• Comparator sorting preserves encounter order of values whose comparison is equal. The comparator is evaluated on pairs including NULL elements, must return a non-NULL signed integer, and propagates evaluation errors.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Typical behavior

SELECT array_sort(ARRAY[3, NULL, 1]) AS "value";

​

Sort strings with a NULL element

SELECT array_sort(ARRAY('pear', CAST(NULL AS VARCHAR), 'apple')) AS sorted_values;

​

Limitations

• Comparator must return an integer and must not return NULL.
• Elements must have comparable types. With natural sorting, the relative order of equal values is not guaranteed. With a comparator, values that compare as equal keep their original relative order.
• MAP elements, including MAP values nested inside compared elements, are outside the supported element types.