Array operation functions|V4.4.2| docs|Distributed Database

Array operation functions

Last Updated：2026-03-06 07:02:42 Updated

Array operation functions perform basic operations on input array data, such as adding elements, removing elements, and searching for elements, and return the modified array data. OceanBase Database supports the following array operation functions: array_append(), array_distinct(), array_remove(), cardinality(), element_at(), string_to_array(), array_agg(), unnest(), split(), and contains().

array_append

array_append() function appends a specified element to the target array. The syntax is as follows:

array_append(arr1, element)

The input parameters are described as follows:

If arr1 is a base array, element must be a base type supported by arrays, including Tinyint, Smallint, Int, Bigint, Float, Double, and VarChar.
If arr1 is a nested array, element must be an array with the same number of nested levels as the subarrays in arr1.

The return value is an array.

Here are some examples:

SELECT array_append([1,2,3], 2);
+--------------------------+
| array_append([1,2,3], 2) |
+--------------------------+
| [1,2,3,2]                |
+--------------------------+
1 row in set

SELECT array_append([1,2,3], -1);
+---------------------------+
| array_append([1,2,3], -1) |
+---------------------------+
| [1,2,3,-1]                |
+---------------------------+
1 row in set

SELECT array_append(["1", "2", "a"], "OceanBase");
+--------------------------------------------+
| array_append(["1", "2", "a"], "OceanBase") |
+--------------------------------------------+
| ["1","2","a","OceanBase"]                  |
+--------------------------------------------+
1 row in set

SELECT array_append([[1,2],[3,4]], [5]);
+----------------------------------+
| array_append([[1,2],[3,4]], [5]) |
+----------------------------------+
| [[1,2],[3,4],[5]]                |
+----------------------------------+
1 row in set

array_distinct

array_distinct() function removes duplicate elements from the target array. The syntax is as follows:

array_distinct(arr1)

The input parameters are described as follows:

arr1 must be an array.

The return value is an array.

If the elements of the array are of different types (for example, a string array and a numeric array), an error will be returned.

Here are some examples:

SELECT array_distinct([1,2,3,2,3]);
+-----------------------------+
| array_distinct([1,2,3,2,3]) |
+-----------------------------+
| [1,2,3]                     |
+-----------------------------+
1 row in set

SELECT array_distinct([null,2,3,null]);
+-----------------------------+
| array_distinct([null,2,3,null]) |
+---------------------------------+
| [NULL,2,3]                      |
+---------------------------------+
1 row in set

SELECT array_distinct([1,2,3,2.0]);
+-----------------------------+
| array_distinct([1,2,3,2.0]) |
+-----------------------------+
| [1,2,3]                     |
+-----------------------------+
1 row in set

SELECT array_distinct([1.1,2.2,3.3,2.2]);
+-------------------------------+
| array_distinct([1.1,2.2,3.3,2.2]) |
+-----------------------------------+
| [1.1,2.2,3.3]                     |
+-----------------------------------+
1 row in set

SELECT array_distinct(["hello", "hi", "hi"]);
+---------------------------------------+
| array_distinct(["hello", "hi", "hi"]) |
+---------------------------------------+
| ["hello","hi"]                        |
+---------------------------------------+
1 row in set

SELECT array_distinct([[1,2],[3,4], [3,4]]);
+--------------------------------------+
| array_distinct([[1,2],[3,4], [3,4]]) |
+--------------------------------------+
| [[1,2],[3,4]]                        |
+--------------------------------------+
1 row in set

SELECT array_distinct([["hello", "world"], ["hi", "what"], ["are you?"], ["are you?"]]);
+----------------------------------------------------------------------------------+
| array_distinct([["hello", "world"], ["hi", "what"], ["are you?"], ["are you?"]]) |
+----------------------------------------------------------------------------------+
| [["hello","world"],["hi","what"],["are you?"]]                                   |
+----------------------------------------------------------------------------------+
1 row in set

array_remove

array_remove() function removes a specified element from the target array. The syntax is as follows:

array_remove(arr1, element)

The input parameters are described as follows:

If arr1 is a base array, element must be a base type supported by arrays, including Tinyint, Smallint, Int, Bigint, Float, Double, and VarChar.
If arr1 is a nested array, element must be an array with the same number of nested levels as the subarrays in arr1.

The return value is an array.

Here are some examples:

SELECT array_remove([1,2,3], 2);
+--------------------------+
| array_remove([1,2,3], 2) |
+--------------------------+
| [1,3]                    |
+--------------------------+
1 row in set

SELECT array_remove([1,2,3], 2.0);
+----------------------------+
| array_remove([1,2,3], 2.0) |
+----------------------------+
| [1,3]                      |
+----------------------------+
1 row in set

SELECT array_remove([1.1,2.2,3.3], 2.2);
+----------------------------------+
| array_remove([1.1,2.2,3.3], 2.2) |
+----------------------------------+
| [1.1,3.3]                        |
+----------------------------------+
1 row in set

SELECT array_remove(["hello", "hi"], "hi");
+-------------------------------------+
| array_remove(["hello", "hi"], "hi") |
+-------------------------------------+
| ["hello"]                           |
+-------------------------------------+
1 row in set

SELECT array_remove([[1,2],[3,4]], [3,4]);
+------------------------------------+
| array_remove([[1,2],[3,4]], [3,4]) |
+------------------------------------+
| [[1,2]]                            |
+------------------------------------+
1 row in set

SELECT array_remove([[1,2],[3,4]], [3.0,4.0]);
+----------------------------------------+
| array_remove([[1,2],[3,4]], [3.0,4.0]) |
+----------------------------------------+
| [[1,2]]                                |
+----------------------------------------+
1 row in set

SELECT array_remove([["hello", "world"], ["hi", "what"], ["are you?"]], ["are you?"]);
+--------------------------------------------------------------------------------+
| array_remove([["hello", "world"], ["hi", "what"], ["are you?"]], ["are you?"]) |
+--------------------------------------------------------------------------------+
| [["hello","world"],["hi","what"]]                                              |
+--------------------------------------------------------------------------------+
1 row in set

cardinality

cardinality() function returns the number of base elements in the target array. If the target array is a nested array, it returns the total number of base elements in all non-empty subarrays. The syntax is as follows:

cardinality(arr1)

The input parameters are described as follows:

arr1 must be an array.

The return value is an integer.

Here are some examples:

SELECT cardinality([1,2,3]);
+----------------------+
| cardinality([1,2,3]) |
+----------------------+
|                    3 |
+----------------------+
1 row in set

SELECT cardinality([1,2,3,NULL]);
+----------------------------+
| cardinality([1,2,3,NULL]) |
+----------------------------+
|                        4 |
+----------------------------+
1 row in set

SELECT cardinality(['a','b','c','d']);
+----------------------------+
| cardinality(['a','b','c','d']) |
+----------------------------+
|                            4 |
+----------------------------+
1 row in set

SELECT cardinality([[1,2,3],[4]]);
+----------------------------+
| cardinality([[1,2,3],[4]]) |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set

SELECT cardinality([['a','b',NULL,'c'],[NULL,'d']]);
+----------------------------------------------+
| cardinality([['a','b',NULL,'c'],[NULL,'d']]) |
+----------------------------------------------+
|                                            6 |
+----------------------------------------------+
1 row in set

SELECT cardinality([[1,2,3],NULL]);
+-----------------------------+
| cardinality([[1,2,3],NULL]) |
+-----------------------------+
|                           3 |
+-----------------------------+
1 row in set

element_at

element_at() function returns the element at the specified position in the target array based on the index. The syntax is as follows:

element_at(arr1, index)

The input parameters are described as follows:

arr1 must be an array.
index specifies the position of the subelement to retrieve, and it must be an integer.

The return value is described as follows:

If arr1 is a nested array, the return type is an array.
If arr1 is a base type, the return type is the corresponding base type.

Here are some examples:

SELECT element_at([1,2,3], 2);
+------------------------+
| element_at([1,2,3], 2) |
+------------------------+
|                      2 |
+------------------------+
1 row in set

SELECT element_at([1,2,3], 4);
+------------------------+
| element_at([1,2,3], 4) |
+------------------------+
|                   NULL |
+------------------------+
1 row in set

SELECT element_at(['a',NULL,'bb','ccc'], 4);
+--------------------------------------+
| element_at(['a',NULL,'bb','ccc'], 4) |
+--------------------------------------+
| ccc                                  |
+--------------------------------------+
1 row in set

SELECT element_at([[1,2],[3,4]], 1);
+------------------------------+
| element_at([[1,2],[3,4]], 1) |
+------------------------------+
| [1,2]                        |
+------------------------------+
1 row in set

SELECT element_at([["hello", "world"], ["hi", "what"], ["are you?"]], 3);
+-------------------------------------------------------------------+
| element_at([["hello", "world"], ["hi", "what"], ["are you?"]], 3) |
+-------------------------------------------------------------------+
| ["are you?"]                                                      |
+-------------------------------------------------------------------+
1 row in set

string_to_array

string_to_array() function converts a character string to an array. Specifically, it splits the input character string into multiple elements based on the specified delimiter and null string, and then places the elements into an array in sequence. The delimiter and null string are case-sensitive. The syntax is as follows:

string_to_array(arr_str, delimiter[, null_str])

The input parameters are described as follows:

arr_str must be a character type, including Char and Varchar.
delimiter specifies the delimiter, which can be a character type, including Char and Varchar.
null_str (optional) specifies the null string, which can be a character type, including Char and Varchar.

The return value is an array of characters.

Here are some examples:

SELECT string_to_array('1and2and3and', 'and');
+----------------------------------------+
| string_to_array('1and2and3and', 'and') |
+----------------------------------------+
| ["1","2","3",""]                       |
+----------------------------------------+
1 row in set

SELECT string_to_array('1,2,3', '');
+------------------------------+
| string_to_array('1,2,3', '') |
+------------------------------+
| ["1,2,3"]                    |
+------------------------------+
1 row in set

SELECT string_to_array('1andNULLand3andNULL', 'and', 'NULL');
+-------------------------------------------------------+
| string_to_array('1andNULLand3andNULL', 'and', 'NULL') |
+-------------------------------------------------------+
| ["1",NULL,"3",NULL]                                   |
+-------------------------------------------------------+
1 row in set

split

split() function splits the string string by the delimiter delimiter and returns an array. The syntax is as follows:

split(string, delimiter)

The parameters are described as follows:

string and delimiter are both of the VARCHAR type.
The return value is of the ARRAY<varchar> type.

Here are some examples:

SELECT split('1#2#3', '#'), split('#1#2#3#', '#'),  split('123', '#');
+---------------------+-----------------------+-------------------+
| split('1#2#3', '#') | split('#1#2#3#', '#') | split('123', '#') |
+---------------------+-----------------------+-------------------+
| ["1","2","3"]       | ["","1","2","3",""]   | ["123"]           |
+---------------------+-----------------------+-------------------+

contains

The contains() function checks if an element exists in the specified container or string and returns a Boolean value. The syntax is as follows:

CONTAINS(array_expr, value)

Examples are provided.

SELECT CONTAINS([1, 2, 3, 4, 5], 3), CONTAINS([1, 2, 3, 4, 5], 6), CONTAINS(['a', 'b', 'c'], 'b'), CONTAINS(['a', 'b', 'c'], 'd');
+------------------------------+------------------------------+--------------------------------+--------------------------------+
| CONTAINS([1, 2, 3, 4, 5], 3) | CONTAINS([1, 2, 3, 4, 5], 6) | CONTAINS(['a', 'b', 'c'], 'b') | CONTAINS(['a', 'b', 'c'], 'd') |
+------------------------------+------------------------------+--------------------------------+--------------------------------+
|                            1 |                            0 |                              1 |                              0 |
+------------------------------+------------------------------+--------------------------------+--------------------------------+

array_agg

The array_agg() function aggregates mult rows of data from specified columns and returns one array value. The syntax is as follows:

array_agg([DISTINCT] col [ORDER BY col0 [DESC | ASC] [NULLS FIRST | NULLS LAST] ...])

The following table describes the input parameters.

col the target column for aggregated data.
DISTINCT (optional). Specifies whether to remove duplicate values from the col column.
ORDER BY (optional) specifies whether to sort the data in the col column.
- DESC (optional) specifies descending order.
- ASC (optional) specifies an ascending sort order.
- NULLS FIRST (optional) specifies that NULL values appear first in the sorted results.
- NULLS LAST (optional). Specifies that NULL values are sorted last.

The result is of array type.

Subject to the following constraints:

The array type is not supported for col0 specified with the ORDER BY clause.
ORDER BY cannot be used to specify the column to sort by using numbers such as 1, 2, .... The ORDER BY clause is ignored if you specify a column by using a number.

The example is given as follows:

SELECT * FROM ss;
+------+------+-----------+-------+--------+
| id   | name | subject   | score | arr    |
+------+------+-----------+-------+--------+
|    1 | Tom  | English   |  90.5 | [1,2]  |
|    1 | Tom  | Math      |  80.8 | [1,2]  |
|    2 | Tom  | English   |  NULL | [1]    |
|    2 | Tom  | NULL      |  NULL | NULL   |
|    3 | May  | NULL      |  NULL | [2]    |
|    3 | Ti   | English   |  98.3 | [NULL] |
|    4 | NULL | NULL      |  NULL | NULL   |
| NULL | NULL | NULL      |  NULL | NULL   |
| NULL | Ti   | Physical Ph      |    99 | [3,4]  |
+------+------+-----------+-------+--------+
9 rows in set

SELECT array_agg(DISTINCT name ORDER BY name ASC), array_agg(name ORDER BY name DESC) FROM ss ORDER BY id;
+--------------------------------------------+-----------------------------------------------------+
| array_agg(distinct name order by name asc) | array_agg(name order by name desc)                  |
+--------------------------------------------+-----------------------------------------------------+
| [NULL,"May","Ti","Tom"]                    | ["Tom","Tom","Tom","Tom","Ti","Ti","May",NULL,NULL] |
+--------------------------------------------+-----------------------------------------------------+
1 row in set

SELECT array_agg(score ORDER BY score DESC NULLS FIRST), array_agg(score ORDER BY score DESC NULLS LAST) FROM ss ORDER BY id;
+--------------------------------------------------+-------------------------------------------------+
| array_agg(score order by score desc nulls first) | array_agg(score order by score desc nulls last) |
+--------------------------------------------------+-------------------------------------------------+
| [NULL,NULL,NULL,NULL,NULL,99,98.3,90.5,80.8]     | [99,98.3,90.5,80.8,NULL,NULL,NULL,NULL,NULL]    |
+--------------------------------------------------+-------------------------------------------------+
1 row in set

SELECT array_agg(arr ORDER BY id), array_agg(DISTINCT arr) FROM ss;
+---------------------------------------------------+-----------------------------------+
| array_agg(arr order by id)                        | array_agg(distinct arr)           |
+---------------------------------------------------+-----------------------------------+
| [NULL,[3,4],[1,2],[1,2],[1],NULL,[2],[NULL],NULL] | [[1,2],[1],NULL,[2],[NULL],[3,4]] |
+---------------------------------------------------+-----------------------------------+
1 row in set

unnest

The unnest() function is used to expand elements in an array into multiple rows and return a relational table containing these elements. The syntax is as follows:

unnest(arr_list) [AS] [table_name[(col_name_list)]]

The input parameters are described as follows:

arr_list specifies one or more array values, separated by commas.
table_name (optional) specifies the name of the table to return; the default is unnest. The AS keyword is optional.
col_name_list (optional) specifies the names of the columns to be returned. The default value is unnest, and values are separated by commas. The number of specified columns must match the number of array values.

The return values are described as follows:

Returns a table with a number of rows equal to the maximum number of elements in any of the input arrays and a number of columns equal to the number of input arrays. Columns with fewer elements are padded with NULL.
For arrays that are nested n levels deep, the unnest function only unfolds the first level (the outermost level) of the array, so the output result is an array with n-1 levels of nesting.
The nth column lists the nth subelement in each array. If the elements of arr_list are base data types, the output type is the same as the type of each subelement. If the elements of arr_list are arrays, the output type is an array.

The following conditions apply:

The unnest() function can only be used in the FROM clause.

The following is an example:

SELECT * FROM unnest([1,2,3]);
+--------+
| unnest |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set

SELECT * FROM unnest([1,2,NULL,3], ['11',NULL,'22']);
+--------+--------+
| unnest | unnest |
+--------+--------+
|      1 | 11     |
|      2 | NULL   |
|   NULL | 22     |
|      3 | NULL   |
+--------+--------+
4 rows in set

SELECT t.* FROM unnest([[1,2],[3],NULL,[4,5,6]]) AS t;
+---------+
| unnest  |
+---------+
| [1,2]   |
| [3]     |
| NULL    |
| [4,5,6] |
+---------+
4 rows in set

SELECT t.* FROM unnest([[1,2],[3],NULL,[4,5,6]], ['hi','hello']) AS t(c1,c2);
+---------+-------+
| c1      | c2    |
+---------+-------+
| [1,2]   | hi    |
| [3]     | hello |
| NULL    | NULL  |
| [4,5,6] | NULL  |
+---------+-------+
4 rows in set

SELECT * FROM unnest([1,2,3]) t1(c1), unnest(['11','22']) AS t2(c2);
+------+------+
| c1   | c2   |
+------+------+
|    1 | 11   |
|    1 | 22   |
|    2 | 11   |
|    2 | 22   |
|    3 | 11   |
|    3 | 22   |
+------+------+
6 rows in set

array_prepend

The array_prepend() function adds an element to the beginning of the array and returns a new array. Its syntax is as follows:

array_prepend(array, element)

The following table describes the input parameters:

array An array value is specified.
element specifies the new element to add. Its data type must be compatible with the data type of the elements in the array specified by the array parameter.

The return value is an array.

The following example shows how to use this procedure:

SELECT array_prepend([1,2,3], 2);
+--------------------------------+
| array_prepend([1,2,3], 2)   |
+--------------------------------+
| [2,1,2,3]                    |
+--------------------------------+
1 row in set

SELECT array_prepend([1,2,3], NULL);
+------------------------------+
| array_prepend([1,2,3], NULL) |
+------------------------------+
| [NULL,1,2,3]                 |
+------------------------------+
1 row in set

SELECT array_prepend(["1", "2", "a"], "OceanBase");
+---------------------------------------------+
| array_prepend(["1", "2", "a"], "OceanBase") |
+---------------------------------------------+
| ["OceanBase","1","2","a"]                   |
+---------------------------------------------+
1 row in set

array_concat

The array_concat() function merges multiple arrays and returns a new array. Syntax:

array_concat(array_list)

Parameters:

array_list specifies one or more array values.

The return value is an array.

Subject to the following constraints:

Nested arrays are supported, but the nesting level of each array must be the same. The result is the merge of elements at the first level (the outermost level).

Example:

SELECT array_concat([1,2,3], [4,5,6]);
+--------------------------------+
| array_concat([1,2,3], [4,5,6]) |
+--------------------------------+
| [1,2,3,4,5,6]                 |
+--------------------------------+
1 row in set

SELECT array_concat([1,2,3], [-4], [5.5,6]);
+--------------------------------------+
| array_concat([1,2,3], [-4], [5.5,6]) |
+--------------------------------------+
| [1,2,3,-4,5.5,6]                     |
+--------------------------------------+
1 row in set

SELECT array_concat([[1,2,3]],[[11],[22,44]]);
+----------------------------------------+
| array_concat([[1,2,3]],[[11],[22,44]]) |
+----------------------------------------+
| [[1,2,3],[11],[22,44]]                 |
+----------------------------------------+
1 row in set

array_compact

The array_compact() function deletes consecutive repeated elements from an array and returns a new array. Syntax:

array_compact(array)

The following table describes the input parameters:

array specifies the array value.

The return value is an array.

Here is an example:

SELECT array_compact([1,2,2,3,3,2]);
+------------------------------+
| array_compact([1,2,2,3,3,2]) |
+------------------------------+
| [1,2,3,2]                    |
+------------------------------+
1 row in set

SELECT array_compact(["hello","hello",NULL,NULL,"OceanBase"]);
+--------------------------------------------------------+
| array_compact(["hello","hello",NULL,NULL,"OceanBase"]) |
+--------------------------------------------------------+
| ["hello",NULL,"OceanBase"]                             |
+--------------------------------------------------------+
1 row in set

SELECT array_compact([[1,2,3,NULL],[4,NULL],[4,NULL]]);
+-------------------------------------------------+
| array_compact([[1,2,3,NULL],[4,NULL],[4,NULL]]) |
+-------------------------------------------------+
| [[1,2,3,NULL],[4,NULL]]                         |
+-------------------------------------------------+
1 row in set

array_sort

The array_sort() function sorts an array in ascending order, placing NULL values at the end. Syntax:

array_sort(arr)

The input parameter is described as follows:

arr specifies an array value.

The return value is an array.

Examples:

SELECT array_sort([2,1,3]);
+---------------------+
| array_sort([2,1,3]) |
+---------------------+
| [1,2,3]             |
+---------------------+
1 row in set

SELECT array_sort([NULL,1,2,NULL,2,NULL,NULL]);
+-----------------------------------------+
| array_sort([NULL,1,2,NULL,2,NULL,NULL]) |
+-----------------------------------------+
| [1,2,2,NULL,NULL,NULL,NULL]             |
+-----------------------------------------+
1 row in set

SELECT array_sort(["hello","hello",NULL,NULL,"OceanBase"]);
+-----------------------------------------------------+
| array_sort(["hello","hello",NULL,NULL,"OceanBase"]) |
+-----------------------------------------------------+
| ["OceanBase","hello","hello",NULL,NULL]             |
+-----------------------------------------------------+
1 row in set

array_length

The array_length() function returns the length of an array. If the array is nested, it returns the length of the outermost array. Syntax:

array_length(arr)

The input parameter is described as follows:

arr specifies an array value. Nested arrays are supported.

The return value is a unit32 unsigned integer.

Examples:

SELECT array_length([1,2,3]);
+-----------------------+
| array_length([1,2,3]) |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set

SELECT array_length([[1],[2,3]]);
+---------------------------+
| array_length([[1],[2,3]]) |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set

array_range

The array_range() function generates an array of evenly spaced numbers. Syntax:

array_range(end)
array_range(start, end)
array_range(start, end, step)

The input parameters are described as follows:

start specifies the starting value. The default value is 0.
end specifies the ending value. Only integer values are supported.
step specifies the step size. Only integer values are supported. The default value is 1.

The return value is an array of BIGINT elements. The output includes the input start value but excludes the end value. If (end - start) / step <= 0, an empty array is returned.

Specific details are as follows:

Generated array: The array_range function generates an array containing consecutive integers, including the start element but excluding the end element. This means the generated array starts with start and ends with end, but end itself is not included in the result.
Step size: If a step size is provided, each element in the generated array will be step greater than the previous element. For example, if start is 1, end is 10, and step is 2, the generated array will be [1, 3, 5, 7, 9].
Condition for returning an empty array: If (end - start) / step <= 0, the function returns an empty array. This means that if end is less than or equal to start, or if the step size step is zero or negative, the generated array will have no valid elements, and an empty array is returned.

Examples:

SELECT array_range(5);
+----------------+
| array_range(5) |
+----------------+
| [0,1,2,3,4]  |
+----------------+
1 row in set

SELECT array_range(-1,4);
+-------------------+
| array_range(-1,4) |
+-------------------+
| [-1,0,1,2,3]      |
+-------------------+
1 row in set

SELECT array_range(-1,4,2);
+---------------------+
| array_range(-1,4,2) |
+---------------------+
| [-1,1,3]            |
+---------------------+
1 row in set

array_sum

The array_sum() function calculates the sum of all elements in an array. Syntax:

array_sum(arr)

The input parameters are described as follows:

arr specifies an array value of numeric elements.
Handling of NULL values:
- If all elements in the array are NULL, NULL is returned.
- If not all elements are NULL, NULL values are treated as 0 in the calculation.

The return value is described as follows:

An array of INT elements.
Or an array of DOUBLE elements.

Functional constraints are as follows:

The array elements must be of integer types (including INT, BIGINT, etc.) or float types (including FLOAT and DOUBLE).
Nested arrays are not supported.

Examples:

SELECT array_sum([1,2,3]);
+--------------------+
| array_sum([1,2,3]) |
+--------------------+
|                  6 |
+--------------------+
1 row in set

SELECT array_sum([1,2.2,3]);
+----------------------+
| array_sum([1,2.2,3]) |
+----------------------+
|                  6.2 |
+----------------------+
1 row in set

array_difference

The array_difference() function calculates the difference between two adjacent elements in an array and stores the result in a new array, which is then returned. Syntax:

array_difference(arr)

The input parameters are described as follows:

arr specifies an array value.

The return value is an array.

Functional constraints are as follows:

The array elements must be of integer types (including INT, BIGINT, etc.) or float types (including FLOAT and DOUBLE).
Nested arrays are not supported.

Specific details are as follows:

Calculation method:
- The first element in the returned array is fixed to 0.
- The value of the nth element in the returned array is array[n] - array[n-1], which means the current element minus the previous element.
Return value:
- A new array where the first element is 0 and the subsequent elements are the differences between adjacent elements.

Examples:

SELECT array_difference([1,5,3]);
+---------------------------+
| array_difference([1,5,3]) |
+---------------------------+
| [0,4,-2]                  |
+---------------------------+
1 row in set

SELECT array_difference([1.1,2.2,4.4]);
+---------------------------------+
| array_difference([1.1,2.2,4.4]) |
+---------------------------------+
| [0,1.1,2.2]                     |
+---------------------------------+
1 row in set

array_min

The array_min() function returns the minimum value in an array. Syntax:

array_min(arr)

The input parameters are described as follows:

arr specifies an array value.
Handling of NULL values:
- If all elements in the array are NULL, NULL is returned.
- If not all elements are NULL, the NULL elements are skipped.

The return value is the same as the input array element type.

Functional constraints are as follows:

Nested arrays are not supported.

Examples:

SELECT array_min([1,2,4]);
+--------------------+
| array_min([1,2,4]) |
+--------------------+
|                  1 |
+--------------------+
1 row in set

SELECT array_min([1.1,2.2,4.4]);
+--------------------------+
| array_min([1.1,2.2,4.4]) |
+--------------------------+
|                      1.1 |
+--------------------------+
1 row in set

SELECT array_min([1,2,NULL,3]);
+-------------------------+
| array_min([1,2,NULL,3]) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set

array_max

The array_max() function returns the maximum value in an array. Syntax:

array_max(arr)

The input parameters are described as follows:

arr specifies an array value.
Handling of NULL values:
- If all elements in the array are NULL, NULL is returned.
- If not all elements are NULL, the NULL elements are skipped.

The return value is the same as the input array element type.

Functional constraints are as follows:

Nested arrays are not supported.

Examples:

SELECT array_max([1,2,4]);
+--------------------+
| array_max([1,2,4]) |
+--------------------+
|                  4 |
+--------------------+
1 row in set

SELECT array_max([1.1,2.2,4.4]);
+--------------------------+
| array_max([1.1,2.2,4.4]) |
+--------------------------+
|                      4.4 |
+--------------------------+
1 row in set

SELECT array_max([1,2,NULL,3]);
+-------------------------+
| array_max([1,2,NULL,3]) |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set

array_avg

The array_avg() function returns the average of all elements in the array. The syntax is as follows:

array_avg(arr)

The input parameters are described as follows:

arr must be an array type.
Handling of NULL values:
- If all elements in the array are NULL, the function returns NULL.
- If not all elements are NULL, the NULL values are treated as 0 during the calculation.

The return value is an array of DOUBLE type.

The following constraints apply to this function:

The array elements must be of integer (INT, BIGINT, etc.) or floating-point (FLOAT, DOUBLE) types.
Nested arrays are not supported.

Here are some examples:

SELECT array_avg([1,2,-4]);
+---------------------+
| array_avg([1,2,-4]) |
+---------------------+
| -0.3333333333333333 |
+---------------------+
1 row in set

SELECT array_avg([1,2,NULL,3]);
+-------------------------+
| array_avg([1,2,NULL,3]) |
+-------------------------+
|                     1.5 |
+-------------------------+
1 row in set

array_position

The array_position() function finds the position of a specified element in the array. The syntax is as follows:

array_position(arr, element)

The input parameters are described as follows:

arr must be an array type.
element is the element to be found, which can be a basic type or an array type supported by the array.

The return value is an array of INT type. If the element does not exist in the array, the function returns 0.

Here are some examples:

SELECT array_position([1,2,3], 2);
+--------------------------------+
| array_position([1,2,3], 2)     |
+--------------------------------+
|                              2 |
+--------------------------------+
1 row in set

SELECT array_position(["hello", "hi"], "hi");
+---------------------------------------+
| array_position(["hello", "hi"], "hi") |
+---------------------------------------+
|                                     2 |
+---------------------------------------+
1 row in set

SELECT array_position(["hello", "hi"], "hel");
+----------------------------------------+
| array_position(["hello", "hi"], "hel") |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set

array_slice

The array_slice() function extracts a specified number of elements from a specified position in an array and returns the extracted elements as a new array. The syntax is as follows:

array_slice(arr, offset, length)

The input parameters are described as follows:

arr: The input value must be of an array type.
offset: The position of the element from which to start extracting elements.
- Positive: Specifies the element starting from the left side.
- Negative: Specifies the element starting from the right side.
length (optional): The maximum number of elements to extract.
- Positive: Specifies the maximum number of elements to extract.
- Negative: Specifies the end boundary from which to extract elements.
- If not provided, all elements starting from the offset position will be extracted.

The return value is described as follows:

The return value is of an array type.
If the offset value is greater than the array length or is 0, an empty array is returned.

Examples are as follows:

-- offset is positive, indicating to start extracting from the 2nd element from the left.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2);
+------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2) |
+------------------------------------+
| [2,3,4,5,6,7,8,9]                  |
+------------------------------------+
1 row in set

-- offset is positive, indicating to start extracting from the 10th element from the left. However, since the offset exceeds the array length, an empty array is returned.
SELECT array_slice([1,2,3,4,5,6,7,8,9],10);
+-------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],10) |
+-------------------------------------+
| []                                  |
+-------------------------------------+
1 row in set

-- offset is 0, returning an empty array.
SELECT array_slice([1,2,3,4,5,6,7,8,9],0);
+------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],0) |
+------------------------------------+
| []                                 |
+------------------------------------+
1 row in set

-- offset is negative, indicating to start extracting from the 2nd element from the right.
SELECT array_slice([1,2,3,4,5,6,7,8,9],-2);
+-------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],-2) |
+-------------------------------------+
| [8,9]                               |
+-------------------------------------+
1 row in set

-- length is positive, indicating to extract 2 elements.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,2);
+--------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,2) |
+--------------------------------------+
| [2,3]                                |
+--------------------------------------+
1 row in set

-- length is positive, indicating to extract 10 elements. However, since the extraction exceeds the right boundary, only the available elements are extracted.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,10);
+---------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,10) |
+---------------------------------------+
| [2,3,4,5,6,7,8,9]                     |
+---------------------------------------+
1 row in set

-- length is -2, indicating to skip the last 2 elements (8 and 9) and extract the rest.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,-2);
+---------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,-2) |
+---------------------------------------+
| [2,3,4,5,6,7]                         |
+---------------------------------------+
1 row in set

-- length is -10, indicating to skip the last 10 elements. However, since this exceeds the left boundary, an empty array is returned.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,-10);
+----------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,-10) |
+----------------------------------------+
| []                                     |
+----------------------------------------+
1 row in set

-- offset is -10, indicating to start extracting from the 10th element from the right. However, since this is one position beyond the left boundary, the elements from this position are extracted for the specified length (4 in this case).
SELECT array_slice([1,2,3,4,5,6,7,8,9],-10,4);
+----------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],-10,4) |
+----------------------------------------+
| [1,2,3]                                |
+----------------------------------------+
1 row in set

-- offset is -6, indicating that the extraction starts from the 6th position from the right, which is 4; length is -4, indicating that the last 4 elements (6,7,8,9) are not taken.
SELECT array_slice([1,2,3,4,5,6,7,8,9],-6,-4);
+-----------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],-6,-4) |
+-----------------------------------------+
| [4,5]                             |
+-----------------------------------------+
1 row in set

reverse

The reverse() function reverses the elements in an array. If the array is nested, the function reverses the elements in the innermost array. The syntax is as follows:

reverse(arr)

The input parameters are described as follows:

arr The input type must be an array. Nested arrays are supported.

The return value is an array.

Here are some examples:

SELECT reverse([1,2,3]);
+----------------+
| reverse([1,2,3]) |
+----------------+
| [3,2,1]         |
+----------------+
1 row in set

SELECT reverse([['a'], ['b'], ['c']]) ;
+--------------------------------+
| reverse([['a'], ['b'], ['c']]) |
+--------------------------------+
| [["c"],["b"],["a"]]            |
+--------------------------------+
1 row in set

array_map

The array_map() function converts each element of the input arrays using the Lambda function and returns a new array containing the converted elements. The syntax is as follows:

arrayMap((x1,...,xn) -> (expression), arr1,...,arrn)

The input parameters are described as follows:

(x1,...,xn) -> (expression) A Lambda function. A Lambda function is an anonymous function that does not require the use of the def statement. Instead, it is created using the lambda keyword. A Lambda function can accept any number of parameters but can only contain a single expression.
arr1,...,arrn The input arrays. The input type must be an array. The number of elements in each array must be the same. The value of n must match the number of parameters in the Lambda function.
For each input array, the i-th element is taken as the input parameter for the Lambda function, and the return value becomes the i-th element of the new array.

The return value is an array. The element type is the return type of the Lambda function. The array length is the same as the number of elements in the input arrays.

The function has the following constraints:

Subqueries are not supported in the expression of the Lambda function.
Aggregate functions are not supported in the expression of the Lambda function.
Expressions for generated columns are not supported in the expression of the Lambda function.

Here are some examples:

SELECT array_map((x,y,z) -> (x is null and y is not null or z is not null), [[1]], [1],['abc']);
+------------------------------------------------------------------------------------------+
| array_map((x,y,z) -> (x is null and y is not null or z is not null), [[1]], [1],['abc']) |
+------------------------------------------------------------------------------------------+
| [1]                                                                                      |
+------------------------------------------------------------------------------------------+
1 row in set

SELECT array_map((x,y) -> (x + y),[1], [2]);
+--------------------------------------+
| array_map((x,y) -> (x + y),[1], [2]) |
+--------------------------------------+
| [3]                                  |
+--------------------------------------+
1 row in set

SELECT array_map((x,y)->((x is null) and (y is null)), [1232], [[['abc']]]);
+----------------------------------------------------------------------+
| array_map((x,y)->((x is null) and (y is null)), [1232], [[['abc']]]) |
+----------------------------------------------------------------------+
| [0]                                                                  |
+----------------------------------------------------------------------+
1 row in set

SELECT array_map((x,y)->(x+y), array_map(x2->(x2+1),[1,2,3]),array_map(x1->(x1+2),[1,2,3]));
+--------------------------------------------------------------------------------------+
| array_map((x,y)->(x+y), array_map(x2->(x2+1),[1,2,3]),array_map(x1->(x1+2),[1,2,3])) |
+--------------------------------------------------------------------------------------+
| [5,7,9]                                                                              |
+--------------------------------------------------------------------------------------+
1 row in set

SELECT array_map(x ->(length(x)), ['abc', 'efgaa']);
+----------------------------------------------+
| array_map(x ->(length(x)), ['abc', 'efgaa']) |
+----------------------------------------------+
| [3,5]                                        |
+----------------------------------------------+
1 row in set

SELECT array_map((x, y)->(floor((y - x) / x)), [4, 5, 6], [3,8,5]);
+-------------------------------------------------------------+
| array_map((x, y)->(floor((y - x) / x)), [4, 5, 6], [3,8,5]) |
+-------------------------------------------------------------+
| [-1,0,-1]                                                   |
+-------------------------------------------------------------+
1 row in set

array_filter

The array_filter() function filters elements in the arr1 array based on the return value of the Lambda function. If the return value is 0 or NULL, the corresponding arr1 element is filtered out, and a new array is returned. The syntax is as follows:

array_filter((x1,...,xn) -> (expression), arr1,...,arrn)

The input parameters are described as follows:

(x1,...,xn) -> (expression) A Lambda function. A Lambda function is an anonymous function that does not require the use of the def statement. Instead, it is created using the lambda keyword. A Lambda function can accept any number of parameters but can only contain a single expression.
arr1,...,arrn The input arrays. The input type must be an array. The number of elements in each array must be the same. The value of n must match the number of parameters in the Lambda function. Nested arrays are supported.

The return value is an array. The array contains the filtered arr1 elements.

The function has the following constraints:

Only integer or NULL return values are supported for the Lambda function.

Here are some examples:

SELECT array_filter(x ->(x + 1 > 2),[1,2,3,4]);
+-----------------------------------------+
| array_filter(x ->(x + 1 > 2),[1,2,3,4]) |
+-----------------------------------------+
| [2,3,4]                                 |
+-----------------------------------------+
1 row in set

SELECT array_filter((x, y) ->(y), [1,2,3,4,5], [NULL,1,-1,0,2]);
+----------------------------------------------------------+
| array_filter((x, y) ->(y), [1,2,3,4,5], [NULL,1,-1,0,2]) |
+----------------------------------------------------------+
| [2,3,5]                                                  |
+----------------------------------------------------------+
1 row in set

SELECT array_filter((x, y) ->(y), [['a'],['b','c'],['d']], [1,0,1]);
+--------------------------------------------------------------+
| array_filter((x, y) ->(y), [['a'],['b','c'],['d']], [1,0,1]) |
+--------------------------------------------------------------+
| [["a"],["d"]]                                                |
+--------------------------------------------------------------+
1 row in set

array_sortby

The array_sortby() function sorts an array based on the given Lambda function. The syntax is as follows:

array_sortby((x1,...,xn) -> (expression), arr1,...,arrn)

The input parameters are described as follows:

(x1,...,xn) -> (expression) A Lambda function. A Lambda function is an anonymous function that does not require the use of the def statement. Instead, it is created using the lambda keyword. A Lambda function can accept any number of parameters but can only contain a single expression.
arr1,...,arrn The input arrays. The input type must be an array. The number of elements in each array must be the same. The value of n must match the number of parameters in the Lambda function. Nested arrays are supported.

The return value is an array.

The function has the following constraints:

Only the basic element types supported by arrays are supported as return values for the Lambda function. Array types are not supported.

The function works as follows:

Generate the sorting criteria: First, array_sortby calculates the result of the Lambda function for each element in the input array and generates a new array containing these results.
Sort the array and handle NULL values: Next, the new array is sorted in ascending order, with NULL values placed at the end. This means that non-NULL values are prioritized during sorting, while NULL values are placed at the end of the sorted result.
Sort the original array based on the new array: Finally, array_sortby sorts the original array based on the sorting order of the new array. This means that the elements in the original array are arranged according to the sorting order of the Lambda function's results.
Return the sorted array: The function returns the sorted original array.

Here are some examples:

SELECT array_sortby(x ->(x), [4,2,1,3]);
+----------------------------------+
| array_sortby(x ->(x), [4,2,1,3]) |
+----------------------------------+
| [1,2,3,4]                        |
+----------------------------------+
1 row in set

SELECT array_sortby(x ->(x), ['c',NULL,'a',NULL]);
+--------------------------------------------+
| array_sortby(x ->(x), ['c',NULL,'a',NULL]) |
+--------------------------------------------+
| ["a","c",NULL,NULL]                        |
+--------------------------------------------+
1 row in set

SELECT array_sortby((x,y) ->(y),['a','b','c'], [2,1,3]);
+--------------------------------------------------+
| array_sortby((x,y) ->(y),['a','b','c'], [2,1,3]) |
+--------------------------------------------------+
| ["b","a","c"]                                    |
+--------------------------------------------------+
1 row in set

SELECT array_sortby((x,y) ->(y),[['a'],['b'],['c']], [2,1,3]);
+--------------------------------------------------------+
| array_sortby((x,y) ->(y),[['a'],['b'],['c']], [2,1,3]) |
+--------------------------------------------------------+
| [["b"],["a"],["c"]]                                    |
+--------------------------------------------------------+
1 row in set

array_first

The array_first() function returns the first element in arr1 for which the given Lambda function returns a value other than 0. The syntax is as follows:

array_first(x1,...,xn) -> (expression), array1,...,arrayn)

The input parameters are described as follows:

(x1,...,xn) -> (expression) specifies a Lambda function.
arr1,...,arrn specifies the input arrays. The type must be an array type, and the number of elements in each array must be the same. n must match the number of parameters in the Lambda function. Nested arrays are supported.

The return value is of any element type.

The function works as follows:

Lambda function: You must provide a Lambda function that evaluates each element in the array. The Lambda function must return an integer.
Nested arrays: The array_first function supports nested arrays, meaning you can pass an array that contains other arrays.
Return value: The function traverses the elements of arr1, finds the first element for which the Lambda function returns a value other than 0, and returns that element as the result. If no element meets this condition, the function may return NULL.

Here are some examples:

SELECT array_first(x ->(x + 1 > 2),[1,2,3,4]);
+----------------------------------------+
| array_first(x ->(x + 1 > 2),[1,2,3,4]) |
+----------------------------------------+
|                                      2 |
+----------------------------------------+
1 row in set

SELECT array_first((x,y) ->(y),[[1],[2],[3]], [0,1,3]);
+-------------------------------------------------+
| array_first((x,y) ->(y),[[1],[2],[3]], [0,1,3]) |
+-------------------------------------------------+
| [2]                                             |
+-------------------------------------------------+
1 row in set

The examples are described as follows:

Lambda function: (x,y) -> (y) is a Lambda function that accepts two parameters, x and y, and returns the value of y. In this specific call, x is not used.
Input arrays:
- [[1],[2],[3]] is a nested array containing three subarrays.
- [0,1,3] is a one-dimensional array containing three integers.
Return value calculation:
- The array_first function traverses the elements of the input array [[1],[2],[3]] and calls the Lambda function for each element, using the corresponding y value from [0,1,3] for evaluation.
- In this example, the Lambda function returns 0, 1, and 3, corresponding to [1], [2], and [3], respectively.
- Therefore, array_first finds the first element in [[1],[2],[3]] for which the Lambda function returns a value other than 0. In this case, the y value for [2] is 1, which is the first non-zero value. The final return value is [2].

array_except

The array_except() function returns an array of elements that are present in arr1 but not in arr2. The syntax is as follows:

array_except(arr1, arr2)

The input parameters are described as follows:

arr1: the left array to be processed.
arr2: the right array to be processed.

The return value is of array type.

The function works as follows:

Nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When arrays are nested, the elements involved in the operation are the first-level arrays. Even if two arrays have the same elements in a different order, they are considered different arrays.
NULL value handling: If any element in the array is NULL, the result is NULL.

Here are some examples:

SELECT array_except([1,2,3], [1,2]);
+------------------------------+
| array_except([1,2,3], [1,2]) |
+------------------------------+
| [3]                          |
+------------------------------+
1 row in set

SELECT array_except(["test", "array"], ["test"]);
+-------------------------------------------+
| array_except(["test", "array"], ["test"]) |
+-------------------------------------------+
| ["array"]                                 |
+-------------------------------------------+
1 row in set

SELECT array_except([[1,2,3],[1,2]], [[1,2],[3,4]]);
+----------------------------------------------+
| array_except([[1,2,3],[1,2]], [[1,2],[3,4]]) |
+----------------------------------------------+
| [[1,2,3]]                                    |
+----------------------------------------------+
1 row in set

SELECT array_except([1,2,3,NULL], [1]);
+---------------------------------+
| array_except([1,2,3,NULL], [1]) |
+---------------------------------+
| [2,3,NULL]                      |
+---------------------------------+
1 row in set

array_intersect

The array_intersect() function calculates the intersection of the first-level elements of all input arrays. The syntax is as follows:

array_intersect(arr_list)

The input parameters are described as follows:

arr_list: the list of arrays to be processed for intersection.

The return value is of array type.

The function works as follows:

Nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When arrays are nested, the elements involved in the operation are the first-level arrays. Even if two arrays have the same elements in a different order, they are considered different arrays.
NULL value handling: No special handling is performed for NULL values.

Here are some examples:

SELECT array_intersect([1,2,3], [1,2]);
+---------------------------------+
| array_intersect([1,2,3], [1,2]) |
+---------------------------------+
| [1,2]                           |
+---------------------------------+
1 row in set

-- The result will automatically deduplicate
SELECT array_intersect([1,1,2,2,3], [1,1,2]);
+---------------------------------------+
| array_intersect([1,1,2,2,3], [1,1,2]) |
+---------------------------------------+
| [1,2]                                 |
+---------------------------------------+
1 row in set

SELECT array_intersect([1,2,4,NULL], [4,5,NULL]);
+-------------------------------------------+
| array_intersect([1,2,4,NULL], [4,5,NULL]) |
+-------------------------------------------+
| [4,NULL]                                  |
+-------------------------------------------+
1 row in set

SELECT array_intersect([[1,2,3], [1,2]], [[1,2],[2,3,4]]);
+----------------------------------------------------+
| array_intersect([[1,2,3], [1,2]], [[1,2],[2,3,4]]) |
+----------------------------------------------------+
| [[1,2]]                                            |
+----------------------------------------------------+
1 row in set

array_union

The array_union() function calculates the union of all input arrays. The syntax is as follows:

array_union(arr_list)

The input parameters are described as follows:

arr_list: the list of arrays to be processed for union.

The return value is of array type.

The function works as follows:

Nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When arrays are nested, the elements involved in the operation are the first-level arrays. Even if two arrays have the same elements in a different order, they are considered different arrays.
NULL value handling: No special handling is performed for NULL values.

Here are some examples:

SELECT array_union([1,2,3], [1,2], [2,3,4]);
+--------------------------------------+
| array_union([1,2,3], [1,2], [2,3,4]) |
+--------------------------------------+
| [1,2,3,4]                            |
+--------------------------------------+
1 row in set

SELECT array_union([1,2,3], [4,5,NULL]);
+----------------------------------+
| array_union([1,2,3], [4,5,NULL]) |
+----------------------------------+
| [1,2,3,4,5,NULL]                 |
+----------------------------------+
1 row in set

SELECT array_union([[1,2,3], [1,2]], [[1,2],[2,3,4]]);
+------------------------------------------------+
| array_union([[1,2,3], [1,2]], [[1,2],[2,3,4]]) |
+------------------------------------------------+
| [[1,2,3],[1,2],[2,3,4]]                        |
+------------------------------------------------+
1 row in set