Version: V1.0.0

Array operations

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

array_append

The array_append() function appends a specified element to an array. Syntax:

array_append(arr1, element)

The input parameters are described as follows:

If arr1 is a basic array, element must be a basic 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 nesting levels as the subarrays of 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 (0.001 sec)

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

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

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

array_distinct

The array_distinct() function removes duplicate elements from an array. Syntax:

array_distinct(arr1)

The input parameters are described as follows:

The type of arr1 must be an array.

The return value is an array.

If the elements of the array are of different types, an error is 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 (0.001 sec)

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

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

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 (0.001 sec)

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

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 (0.001 sec)

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 (0.001 sec)

array_remove

The array_remove() function removes a specified element from an array. Syntax:

array_remove(arr1, element)

The input parameters are described as follows:

If arr1 is a basic array, element must be a basic 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 nesting levels as the subarrays of 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 (0.001 sec)

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

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 (0.001 sec)

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

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

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 (0.001 sec)

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 (0.001 sec)

cardinality

The cardinality() function returns the number of basic elements in the target array. If the target array is a nested array, the function returns the sum of the number of basic elements in all non-empty subarrays. Syntax:

cardinality(arr1)

The input parameters are described as follows:

The type of 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 (0.001 sec)

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

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

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

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

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

element_at

The element_at() function returns the element at the specified index in the target array. Syntax:

element_at(arr1, index)

The input parameters are described as follows:

The type of arr1 must be an array.
index specifies the position of the subelement to be retrieved. The value of index must be an integer.

The return value is described as follows:

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

Here are some examples:

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

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

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

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

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 (0.001 sec)

string_to_array

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

string_to_array(arr_str, delimiter[, null_str])

The input parameters are described as follows:

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

The return value is an array whose elements are characters.

Here are some examples:

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

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

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

split

The split() function is used to split a string string by a delimiter delimiter and return an array. Syntax:

split(string, delimiter)

The parameters are described as follows:

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

For example:

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 is used to check whether an element exists in a specified container or string, and it returns a boolean value. The syntax is as follows:

CONTAINS(array_expr, value)

Example:

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 is used to aggregate the values in multiple rows of a specified column into a single array value and return the result. The syntax is as follows:

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

The input parameters are described as follows:

col Target column for aggregated data.
DISTINCT (optional). Specifies whether to deduplicate data in the col column.
ORDER BY (optional): specifies whether to perform sorting on the col column.
- DESC (optional) specifies a descending order.
- ASC (optional): specifies the ascending order.
- NULLS FIRST (optional) specifies to sort NULL values first.
- NULLS LAST (optional). Specifies to sort NULL values last in the result set.

The return value is of the array type.

The constraints are as follows:

The column specified by ORDER BY, col0, does not support an array data type.
ORDER BY does not support specifying columns to sort by using numbers such as 1, 2, .... If numbers are used, the ORDER BY clause will be ignored.

Here is an example:

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   | Phy    |    99 | [3,4]  |
+------+------+-----------+-------+--------+
9 rows in set (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

unnest

The unnest() function expands the elements of an array into multiple rows and returns a relation table containing these elements. The syntax is as follows:

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

The following table describes the input parameters.

arr_list specifies one or more array values, separated by commas.
table_name (optional) specifies the name of the returned table. The default value is unnest. You can omit the AS keyword.
col_name_list (Optional) specifies the name for each returned column. The default value is unnest, and columns are separated by commas. The number of columns must be the same as the number of values in the array.

The return value is described as follows:

*Returns a table with the number of rows equal to the maximum number of elements in any array of the input arrays, and the number of columns equal to the number of input arrays. The columns with fewer elements than the maximum are filled with NULL values.

If the input is a nested array that has n layers, unnest only un-nests the topmost array (layer 0), returning an array that has n-1 layers.
Each array has the n-th sub-element in row n. If arr_list contains the basic data type, the output type is the corresponding element type. If the array contains the array type, the output type is the array type.

Constraint:

The unnest() function is applicable only to FROM subclauses.

Example:

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

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

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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

array_prepend

The array_prepend() function prepends an element to an array and returns the new array. The syntax is as follows:

array_prepend(array, element)

The input parameters are described as follows:

array specifies an array value.
element specifies the new element that is added. It must have the same data type as the array element in the array parameter.

The return value is an array.

Here is an example:

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

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

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

array_concat

The array_concat() function merges multiple arrays and returns a new array. The syntax is as follows:

array_concat(array_list)

The input parameters are described as follows:

array_list specifies one or more arrays.

The return value is of an array type.

The following constraint applies:

Nested array merging is supported, but the nesting levels of the arrays must be the same. The merged result contains the elements of the first level (outermost level) of the input arrays.

Here is an 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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

array_compact

The array_compact() function removes consecutive duplicate elements from an array and returns a new array. The syntax is as follows:

array_compact(array)

The input parameters are described as follows:

array: specifies the array value.

The return value is of the array type.

Here are some examples:

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 (0.001 sec)

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

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 (0.001 sec)

array_sort

The array_sort() function sorts an array in ascending order and places NULL values at the end of the array. The syntax is as follows:

array_sort(arr)

The input parameters are described as follows:

arr: specifies the array value.

The return value is of the array type.

Here are some examples:

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

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 (0.001 sec)

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

array_length

The array_length() function returns the length of an array. If the array is nested, it returns the length of the top-level array. The syntax is as follows:

array_length(arr)

The input parameters are described as follows:

arr: specifies the array value. Nested arrays are supported.

The return value is of the unit32 unsigned integer type.

Here are some examples:

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

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

array_range

The array_range() function generates an array of consecutive integers. The syntax is as follows:

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

The input parameters are described as follows:

start: the starting value, defaulting to 0.
end: the ending value, which must be an integer.
step: the step size, which must be an integer, defaulting to 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: array_range generates an array containing consecutive integers, starting with the start value and excluding the end value. This means the generated array starts at start and ends at end, but end itself is not included in the result.
Step size: If a step size (step) 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].
Conditions 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 thus an empty array is returned.

Here are some examples:

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

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

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

array_sum

The array_sum() function calculates the sum of all elements in an array. The syntax is as follows:

array_sum(arr)

The input parameters are described as follows:

arr: specifies the array value of numeric elements.
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 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 (INT, BIGINT, etc.) or float types (FLOAT, DOUBLE).
Nested arrays are not supported.

Here are some examples:

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

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

array_difference

The array_difference() function calculates the difference between two adjacent elements in an array and returns a new array containing the results. The syntax is as follows:

array_difference(arr)

The input parameters are described as follows:

arr: specifies the array value.

The return value is of the array type.

Functional constraints are as follows:

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

Specific details are as follows:

Calculation method:
- The first element of the returned array is fixed at 0.
- The value of the nth element in the returned array is array[n] - array[n-1], which is 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.

Here are some examples:

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

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 (0.001 sec)

array_min

The array_min() function returns the minimum value in an array. The syntax is as follows:

array_min(arr)

The input parameters are described as follows:

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

The return value is of the same type as the elements in the input array.

Functional constraints are as follows:

Nested arrays are not supported.

Here are some examples:

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

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

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

array_max

The array_max() function returns the maximum value in an array. The syntax is as follows:

array_max(arr)

The input parameters are described as follows:

arr: The input parameter must be an array.
Handling of NULL values:
- If all elements in the array are NULL, NULL is returned.
- If not all elements in the array are NULL, the NULL values are skipped.

The return value is the type of the array elements.

The following functional constraints apply:

Nested arrays are not supported.

Here are some examples:

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

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

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

array_avg

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

array_avg(arr)

The input parameters are described as follows:

arr: The input parameter must be an array.
Handling of NULL values:
- If all elements in the array are NULL, NULL is returned.
- If not all elements in the array are NULL, the NULL values are treated as 0 in the calculation.

The return value is an array of DOUBLE values.

The following functional constraints apply:

The array elements must be of the INT or DOUBLE type.
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 (0.001 sec)

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

array_position

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

array_position(arr, element)

The input parameters are described as follows:

arr: The input parameter must be an array.
element: The element to be found. The basic types and array types supported by arrays are supported.

The return value is an array of INT values. If the element does not exist, 0 is returned.

Here are some examples:

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

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

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

array_slice

The array_slice() function extracts a portion of elements from an array starting at a specified position and returns the extracted elements as a new array. Syntax:

array_slice(arr, offset, length)

The input parameters are described as follows:

arr must be an array.
offset specifies the starting element:
- Positive value specifies the element at position offset from the left.
- Negative value specifies the element at position -offset from the right.
length specifies the limit (optional parameter):
- Positive value specifies the maximum number of elements to extract.
- Negative value specifies the right boundary, i.e., the element at position -length from the right.
- If not specified, all elements starting from offset are included.

The return values are described as follows:

The returned value is of the array type.
If offset > array length or offset = 0, an empty array is returned.

Examples:

-- offset is a positive integer, indicating the second element from the left (starting from 2)
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 (0.001 sec)

-- offset is a positive integer, indicating the 10th element from the left. Since offset is greater than 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 (0.001 sec)

-- 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 (0.001 sec)

-- offset is a negative integer, indicating the second element from the right (starting from 8)
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 (0.001 sec)

-- length is a positive integer, indicating that 2 elements will be extracted.
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 (0.001 sec)

-- length is a positive integer, indicating that 10 elements will be extracted. However, since it exceeds the right boundary, it will be truncated.
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 (0.001 sec)

-- length is -2, indicating that the last 2 elements (8 and 9) will not be extracted.
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 (0.001 sec)

-- length is -10, indicating that the last 10 elements will not be extracted. However, this exceeds the left boundary, so 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 (0.001 sec)

-- offset is -10, indicating the 10th element from the right. Since this is one position beyond the left boundary, it starts extracting from this position, taking 4 elements (length).
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 (0.001 sec)

-- offset is -6, which indicates that the extraction starts at the 6th position from the right, which is 4. length is -4, which indicates that the last 4 elements are not extracted, that is, 6, 7, 8, and 9 are not extracted.
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 (0.001 sec)

reverse

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

reverse(arr)

The input parameters are described as follows:

arr The input 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 (0.001 sec)

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

array_map

The array_map() function converts each element in the input arrays using a 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 when defined. 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 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 is the i-th element of the new array.

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

The following constraints apply to the function:

The expression in the lambda function does not support subqueries.
The expression in the lambda function does not support aggregate functions.
The expression in the lambda function does not support expressions used to generate columns.

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 (0.001 sec)

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

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 (0.001 sec)

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 (0.001 sec)

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

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 (0.001 sec)

array_filter

The array_filter() function filters elements in the arr1 array based on the return value of the lambda function. If the lambda function returns 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 when defined. 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 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 elements of arr1.

The following constraints apply to the function:

Only integer or NULL values are supported as return values of 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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

array_sortby

The array_sortby() function sorts an array based on the provided 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 when defined. 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 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 following constraints apply to the function:

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

The specific working process is 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 the results.
Sort the array in ascending order and handle NULL values: Next, the new array is sorted in ascending order, with NULL values placed at the end. This means that all 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 rearranged 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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

array_first

The array_first() function returns the first element in the arr1 array for which the given Lambda function returns a value that is not 0. Syntax:

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 of the input arrays must be an array type, and the number of elements in each array must be the same. The number of parameters in the Lambda function must be the same as the number of parameters in the array_first() function. Nested arrays are supported.

The return value is of any element type.

The function is described as follows:

Lambda function: You need to provide a Lambda function that evaluates each element in the array. The return value of the Lambda function must be an integer.
Nested arrays: The array_first() function supports nested arrays. This means that you can pass an array that contains other arrays.
Return value: The function traverses the elements in arr1 to find the first element for which the Lambda function returns a value that is not 0. It returns this element as the result. If no element satisfies 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 (0.001 sec)

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 (0.001 sec)

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, the parameter x is not used.
Input arrays:
- [[1],[2],[3]] is a nested array that contains three subarrays.
- [0,1,3] is a one-dimensional array that contains three integers.
Calculation of the return value:
- The array_first() function traverses each element in 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 return values of the Lambda function are 0, 1, and 3, corresponding to [1], [2], and [3], respectively.
- Therefore, the array_first() function finds the first element in [[1],[2],[3]] for which the Lambda function returns a value that is not 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 belong to arr1 but not to arr2. Syntax:

array_except(arr1, arr2)

The input parameters are described as follows:

arr1: the left array for the operation.
arr2: the right array for the operation.

The return value is of array type.

The function is described as follows:

Nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When the arrays are nested arrays, the elements involved in the operation are the first-level arrays. Even if two arrays have the same elements in different orders, they are considered different arrays.
Handling of NULL values: 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 (0.001 sec)

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

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 (0.001 sec)

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

array_intersect

The array_intersect() function calculates the intersection of the first-level elements of all input arrays. Syntax:

array_intersect(arr_list)

The input parameters are described as follows:

arr_list: the list of arrays for the intersection operation.

The return value is of array type.

The function is described as follows:

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

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 (0.001 sec)

-- The result will automatically remove duplicates.
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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

array_union

The array_union() function calculates the union of all input arrays. Syntax:

array_union(arr_list)

The input parameters are described as follows:

arr_list: the list of arrays for the union operation.

The return value is of array type.

The function is described as follows:

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

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 (0.001 sec)

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 (0.001 sec)

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 (0.001 sec)

array_append​

array_distinct​

array_remove​

cardinality​

element_at​

string_to_array​

split​

contains​

array_agg​

unnest​

array_prepend​

array_concat​

array_compact​

array_sort​

array_length​

array_range​

array_sum​

array_difference​

array_min​

array_max​

array_avg​

array_position​

array_slice​

reverse​

array_map​

array_filter​

array_sortby​

array_first​

array_except​

array_intersect​

array_union​

Contents

array_append

array_distinct

array_remove

cardinality

element_at

string_to_array

split

contains

array_agg

unnest

array_prepend

array_concat

array_compact

array_sort

array_length

array_range

array_sum

array_difference

array_min

array_max

array_avg

array_position

array_slice

reverse

array_map

array_filter

array_sortby

array_first

array_except

array_intersect

array_union