Array functions|V4.6.0|OceanBase Database| docs|Distributed Database

Array functions

Last Updated：2026-05-07 11:26:25 Updated

Array 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 functions: 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 a target array. Syntax:

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

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

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

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

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

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

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

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

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

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

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

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. It 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 base type, the return value 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

The string_to_array() function converts a character string into an array. Specifically, it splits the input character string into multiple elements based on a 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:

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

The return value is an array of character strings.

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

The split() function splits a string string by a delimiter delimiter and returns an array. Syntax:

split(string, delimiter)

The parameters are described as follows:

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

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 whether an element exists in a specified container or string, and 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 returns an array value consisting of all row values of a specified column. Syntax:

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

Parameters:

col Target column for aggregate data.
DISTINCT (optional). Specifies whether to remove duplicate values from the col column.
ORDER BY (optional) specifies whether to sort the values in the col column.
- DESC (optional) specifies descending sort order.
- ASC (optional) specifies that the data be sorted in ascending order.
- NULLS FIRST (optional): specifies that the values of NULL are placed at the beginning during sorting.
- NULLS LAST (optional) specifies that NULL values appear last in the sort.

The return value is an array type.

Limitations and considerations

The col0 column of the ORDER BY clause does not support an array type.
You cannot use numeric values, such as 1, 2, ..., to specify the columns to be sorted in an ORDER BY clause. If you use numeric values, they are ignored.

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

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 expands elements in 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 parameters of the DELETE_SCHEMA_STATS procedure:

The arr_list parameter specifies one or more array values, separated by commas.
table_name (optional) specifies the name of the table whose schema statistics are to be deleted. The default value is unnest. The AS keyword is optional and does not affect the execution of the procedure.
col_name_list (Optional) specifies the name of each column to be returned as a comma-separated list. The default value is unnest. The number of column names must match the number of array values.

The return value description is as follows:

Return a table with the number of rows equal to the maximum size of the elements in the input array and the number of columns equal to the number of input arrays. If the array has fewer elements than the maximum size of the arrays, NULL is used to fill the empty cells in the rows.
For a nested array of n levels, the unnest function only unfolds the first level (i.e. the outermost level) of the array, so the output result is a nested array of n-1 levels.
Line n contains the nth subelement of each array. If the elements of arr_list are of the base data type, the output is of the same data type as the corresponding element. If the elements of arr_list are arrays, the output is of array type.

The constraints are as follows:

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

Here 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 at the beginning of an array and returns a new array. The syntax is as follows:

array_prepend(array, element)

The input parameter is described as follows:

array specifies an array value.
element specifies the new element to be added, and its type must be the same as the element type of the array specified by the array parameter.

The return value is an array type.

Example:

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 is used to merge multiple arrays into a new array. Syntax:

array_concat(array_list)

The parameters of the DELETE_SCHEMA_STATS procedure are described as follows:

array_list specifies one or more array values.

The return value is an array type.

Constraint:

Nesting is supported for array merge, but the nesting level of each array must be the same. The output result is the merged elements from the first (outermost) level of the arrays.

For 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 removes consecutive duplicate elements from an array and returns a new array. Syntax is as follows:

array_compact(array)

The following describes the input parameter:

array specifies an array value.

The return value is of array type.

For 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. The syntax is as follows:

array_sort(arr)

The input parameters are described as follows:

arr specifies an array value.

The return value is an array.

Here are some 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. For nested arrays, 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 an array value. Nested arrays are supported.

The return value is an unsigned 32-bit integer.

Here are some 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 values. 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 specifies the starting value, with a default value of 0.
end specifies the ending value, which must be an integer.
step specifies the step size, which must be an integer, with a default value of 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.

Here are some details:

Generated array: array_range generates an array of consecutive integers, including the start element but excluding the end element. 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 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.

Here are some 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. The syntax is as follows:

array_sum(arr)

The input parameters are described as follows:

arr specifies an array of numeric elements.
Handling of NULL values:
- If all elements are NULL, the result is NULL.
- 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.

The function has the following constraints:

The array elements must be of integer types (INT, BIGINT, etc.) or floating-point 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

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 adjacent elements in an array and returns the result as a new array. The syntax is as follows:

array_difference(arr)

The input parameters are described as follows:

arr specifies an array.

The return value is an array.

The function has the following constraints:

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

Here are some details:

Calculation method:
- The first element of the returned array is fixed at 0.
- The nth element of 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 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

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. The syntax is as follows:

array_min(arr)

The input parameters are described as follows:

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

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

The function has the following constraints:

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

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. The syntax is as follows:

array_max(arr)

The input parameters are described as follows:

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

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

The function has the following constraints:

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

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

array_avg() 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.
How NULL values are handled:
- If all elements in the array are NULL, the function returns NULL.
- If not all elements are NULL, the function treats NULL values as 0 during the calculation.

The return value is an array of DOUBLE values.

The following limitations apply:

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

array_position() 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 find. It can be a basic type or an array type supported by the array.

The return value is an array of INT values. If the element is not found, 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 portion of elements from a specified position in an array and returns a new array containing the extracted elements. The syntax is as follows:

array_slice(arr, offset, length)

The input parameters are described as follows:

arr must be an array type.
offset specifies the starting position for extraction:
- A positive number indicates the element at the offsetth position from the left.
- A negative number indicates the element at the -offsetth position from the right.
length specifies the maximum number of elements to extract (optional parameter):
- A positive number indicates the maximum number of elements to extract.
- A negative number indicates the right boundary for extraction, which is the -lengthth element from the right.
- If not specified, all elements starting from offset are extracted.

The return value is described as follows:

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

Here are some examples:

-- offset is a positive number, indicating the 2nd 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

-- offset is a positive number, indicating the 10th element from the left, which is beyond 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 a negative number, indicating the 2nd 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

-- length is a positive number, 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 a positive number, indicating to extract 10 elements, which exceeds the right boundary.
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, i.e., 8 and 9.
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, which 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, which is beyond the left boundary by 1 position. The length (4) elements starting from this position are extracted.
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 of an array. If the array is nested, it reverses the innermost elements. The syntax is as follows:

reverse(arr)

The input parameters are described as follows:

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

The return value is of the array type.

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 a Lambda function and combines all the new elements into one new array as the output. 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 but instead uses the lambda keyword to create. A Lambda function can accept any number of parameters but only has one expression.
arr1,...,arrn The input arrays. The input 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.
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 of the array type, with elements of the type returned by the Lambda function. The length of the array is the same as the number of elements in the input arrays.

The following constraints apply:

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 for generated 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

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 of 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 but instead uses the lambda keyword to create. A Lambda function can accept any number of parameters but only has one expression.
arr1,...,arrn The input arrays. The input 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 the array type, representing the filtered arr1 array.

The following constraints apply:

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

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 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 but instead uses the lambda keyword to create. A Lambda function can accept any number of parameters but only has one expression.
arr1,...,arrn The input arrays. The input 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 the array type.

The following constraints apply:

Only the base element types supported by arrays are supported as return values from the Lambda function; array types are not supported.

The specific workflow is as follows:

Generate the sorting criteria: First, array_sortby calculates each element of the input array using the provided Lambda function and generates a new array containing the results of this calculation for each element.
Sort 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 during sorting, all non-NULL values are prioritized, and NULL values are placed at the end of the sorted result.
Reorder the original array based on the sorting criteria: Finally, array_sortby reorders the original array based on the sorting order of the new array. This means that the elements of the original array are rearranged according to the sorting order of the Lambda function's calculated 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 the arr1 array 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. The number of parameters n must match the number of parameters in the Lambda function. Nested arrays are supported.

The return value is of any element type.

The specific details are 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.
Support for nested arrays: The array_first function supports nested arrays, which means you can pass an array that contains other arrays.
Return value: The function traverses the elements in 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, it 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 specific details of the examples are 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.
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 0 (for [1]), 1 (for [2]), and 3 (for [3]).
- 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 an array type.

The specific details are as follows:

Support for nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When the arrays are nested, the elements involved in the operation are the first-level arrays. Even if the elements of the two arrays are the same but in a different order, 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

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 the intersection.

The return value is of an array type.

The specific details are as follows:

Support for nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When the arrays are nested, the elements involved in the operation are the first-level arrays. Even if the elements of the two arrays are the same but in a different order, 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

-- 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 the union.

The return value is of an array type.

The specific details are as follows:

Support for nested arrays: The input arrays can be nested arrays, but all arrays must have the same level of nesting. When the arrays are nested, the elements involved in the operation are the first-level arrays. Even if the elements of the two arrays are the same but in a different order, 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

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

OceanBase

Customer Stories

Documentation

Array functions

array_append

array_distinct

array_remove

cardinality

element_at

string_to_array

split

contains

array_agg

Limitations and considerations

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