MaxCompute:ARRAY_SORT

Syntax

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

Parameters

• a: required. This parameter specifies an array. T in array<T> specifies the data type of the elements in the array. The elements can be of any data type.

• comparator: required. This parameter specifies a built-in function, user-defined function (UDF), or expression that is used to compare two elements in the array.

  Processing logic of comparator(a, b): If a is equal to b, 0 is returned. If a is less than b, a negative integer is returned. If a is greater than b, a positive integer is returned. If comparator(a, b) returns null, an error is returned.

  Important

  Comparison functions in ARRAY_SORT must be self-consistent. Examples:

  • If compare(a, b) > 0 is met, compare(b, a) < 0 must be met.

  • If compare(a, b) = 0 is met, compare(b, a) = 0 must be met.

  • If compare(a, b) < 0 is met, compare(b, a) > 0 must be met.

  Examples of functions that are not self-consistent:

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

    If you configure a = 1 and b = 1, the compare(a, b) function returns -1, and the compare(b, a) function also returns -1. The comparison results conflict with each other, and the functions are not self-consistent.

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

    If you configure a = NULL and b = 1, the compare(a, b) function returns 1 and the compare(b, a) function also returns 1. The comparison results conflict with each other, and the functions are not self-consistent.

Return value

A value of the ARRAY type is returned.

Examples

• Example 1: Sort the elements in array(5,6,1).

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

  The following result is returned:

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

• Example 2:

  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);

  The following result is returned:

  +------+
  | _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 the functions that are used to process data of complex data types, such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.

• In the preceding examples, the combination of a hyphen and a closing angle bracket (->) is used. For more information about how to use the combination of a hyphen and a closing angle bracket (->) in Lambda functions, see Lambda functions.