MaxCompute:ARRAY_SORT

Syntax

array<T> array_sort(array<T> <a>, function<T, T, bigint> <comparator>)

Parameters

• a: Required. The array to sort. T in array<T> is the element data type. Elements can be of any data type.

• comparator: Required. A built-in function, user-defined function (UDF), or Lambda expression that compares two elements. Given elements a and b, the comparator returns a negative integer if a < b, 0 if a = b, or a positive integer if a > b. If the comparator returns null, an error is returned.

  Important

  The comparator must be self-consistent. All three of the following conditions must hold:

  Condition	Required inverse
  compare(a, b) < 0	compare(b, a) > 0
  compare(a, b) = 0	compare(b, a) = 0
  compare(a, b) > 0	compare(b, a) < 0

  The following examples show comparators that are not self-consistent:

  • (left, right) -> CASE WHEN left <= right THEN -1L ELSE 0L END When a = 1 and b = 1: compare(a, b) returns -1 and compare(b, a) also returns -1. The results conflict.

  • (left, right) -> CASE WHEN left < right THEN -1L WHEN left = right THEN 0L ELSE 1L END When a = NULL and b = 1: compare(a, b) returns 1 and compare(b, a) also returns 1. The results conflict.

  A correct ascending comparator:

  (left, right) -> CASE WHEN left < right THEN -1L WHEN left > right THEN 1L ELSE 0L END

Return value

Returns a value of the ARRAY type.

Examples

Example 1: Sort the integers in array(5, 6, 1) in ascending order.

SELECT array_sort(array(5, 6, 1),
                  (left, right) ->
                    CASE
                      WHEN left < right THEN -1L
                      WHEN left > right THEN  1L
                      ELSE 0L
                    END
                 );

Result:

+------------+
| _c0        |
+------------+
| [1,5,6]    |
+------------+

Example 2: Sort an array of structs by field a in ascending order.

SELECT array_sort(a,
                  (a, b) ->
                    CASE
                      WHEN a.a < b.a THEN -1L
                      WHEN a.a > b.a THEN  1L
                      ELSE 0L
                    END)
FROM VALUES (
  ARRAY(named_struct('a', 1, 'b', 10),
        named_struct('a', 3, 'b', 11),
        named_struct('a', 2, 'b', 12)))
AS t(a);

Result:

+------+
| _c0 |
+------+
| [{a:1, b:10}, {a:2, b:12}, {a:3, b:11}] |
+------+

Related functions

ARRAY_SORT is a complex type function. For more information about functions that process ARRAY, MAP, STRUCT, and JSON data, see Complex type functions.

All examples above use the -> syntax for Lambda expressions. For usage details, see Lambda functions.