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 the target array. Syntax:
array_append(arr1, element)
The input parameters are described as follows:
- If
arr1is a base array,elementmust be a base type supported by arrays, including Tinyint, Smallint, Int, Bigint, Float, Double, and VarChar. - If
arr1is a nested array,elementmust be an array with the same number of nested levels as the subarrays ofarr1.
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 the target array. Syntax:
array_distinct(arr1)
The input parameters are described as follows:
arr1must 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 the target array. Syntax:
array_remove(arr1, element)
The input parameters are described as follows:
- If
arr1is a base array,elementmust be a base type supported by arrays, including Tinyint, Smallint, Int, Bigint, Float, Double, and VarChar. - If
arr1is a nested array,elementmust be an array with the same number of nested levels as the subarrays ofarr1.
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 the target array. If the target array is a nested array, it returns the sum of the number of base elements in all its non-empty subarrays. Syntax:
cardinality(arr1)
The input parameters are described as follows:
arr1must 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 the specified position in the target array based on the index. Syntax:
element_at(arr1, index)
The input parameters are described as follows:
arr1must be an array.indexspecifies the position of the subelement to retrieve. It must be an integer.
The return value is described as follows:
- If
arr1is a nested array, the return value is an array. - If
arr1is 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 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:
arr_strmust be a character type, including Char and Varchar.delimiterspecifies the delimiter, which must be a character type, including Char and Varchar.null_str(optional) specifies the null string, which must be a character type, including Char and Varchar.
The return value is an array of characters.
Here are some examples:
SELECT string_to_array('1and2and3and', 'and');
+----------------------------------------+
| string_to_array('1and2and3and', 'and') |
+----------------------------------------+
| ["1","2","3",""] |
+----------------------------------------+
1 row in set
SELECT string_to_array('1,2,3', '');
+------------------------------+
| string_to_array('1,2,3', '') |
+------------------------------+
| ["1,2,3"] |
+------------------------------+
1 row in set
SELECT string_to_array('1andNULLand3andNULL', 'and', 'NULL');
+-------------------------------------------------------+
| string_to_array('1andNULLand3andNULL', 'and', 'NULL') |
+-------------------------------------------------------+
| ["1",NULL,"3",NULL] |
+-------------------------------------------------------+
1 row in set
split
The split() function splits the string string by the delimiter delimiter and returns an array. Syntax:
split(string, delimiter)
The parameters are described as follows:
stringanddelimiterare bothVARCHARtypes.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. Its syntax is as follows:
CONTAINS(array_expr, value)
Examples are shown as follows:
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 multiple rows of data under a specified column into a single array value and returns 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:
colspecifies the aggregate target column.DISTINCT(optional) specifies whether to perform a distinct count operation on thecolcolumn.ORDER BY(optional) specifies whether to sort the values incolcolumn.DESC(optional) specifies the sort order. The default isASC, which specifies ascending order.ASC(optional): specifies the order to sort in ascending order.NULLS FIRST(optional). Specifies to placeNULLvalues at the beginning of the sort order.NULLS LAST(optional) specifies thatNULLvalues appear at the end of the sort order.
The return value is of the array type.
The following table describes the rules for schema statistics.
- Array types are not supported for
col0specified byORDER BY. - The
ORDER BYclause does not support specifying the order by columns by using numbers, such as1, 2, .... If you specify numbers inORDER BY, these numbers 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 | Physical 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 the elements in an array to multiple rows and returns a relation table that contains the elements. Syntax:
unnest(arr_list) [AS] [table_name[(col_name_list)]]
The input parameters are described as follows:
arr_listis a list of one or more array values separated by commas.table_name(optional) specifies the name of the table from which the results are returned. The default value isunnest. TheASkeyword is optional and can be omitted without changing the function.col_name_list(optional): a list of column names to be returned, separated by commas. The default isunnest. The number of specified column names must match the number of elements in the array values.
The return value is described as follows:
- Returns a table with rows equal to the maximum number of elements in the input array and columns equal to the number of arrays provided. If an array has fewer elements than the maximum, the empty rows in that column are filled with
NULL. - For an array with n levels of nesting, the
unnestfunction only un-nests the first level (the outermost array), resulting in an array with n-1 levels of nesting. - The content of row n is the nth subelement of each array. If the elements of
arr_listare basic data types, the output type is the type of each corresponding element. If the elements ofarr_listare arrays, the output type is the array type.
The following constraints apply:
- The
unnest()function can only be used after theFROMclause.
The following example illustrates how to use this procedure:
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 returns a new array after adding an element to the beginning of an array. The syntax is as follows:
array_prepend(array, element)
The following table describes the input parameters of the DELETE_SCHEMA_STATS procedure:
arrayspecifies an array value.elementspecifies the element that is to be added. It must be of the same type as the elements in thearrayparameter.
The return type 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
SELECT array_prepend([1,2,3], NULL);
+------------------------------+
| array_prepend([1,2,3], NULL) |
+------------------------------+
| [NULL,1,2,3] |
+------------------------------+
1 row in set
SELECT array_prepend(["1", "2", "a"], "OceanBase");
+---------------------------------------------+
| array_prepend(["1", "2", "a"], "OceanBase") |
+---------------------------------------------+
| ["OceanBase","1","2","a"] |
+---------------------------------------------+
1 row in set
array_concat
The array_concat() function merges multiple arrays into a single array. Here is the syntax:
array_concat(array_list)
The following table describes the input parameters:
array_listspecifies one or more array values.
The return type is an array.
Subject to the following constraints:
- Supports the merging of nested arrays, with each array having the same level of nesting, and the result is the merged result of the elements at the first level (i.e., the outermost level).
The following examples show the procedure:
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. The syntax is as follows:
array_compact(array)
The following table describes the input parameters:
arrayspecifies the array value.
The return value is of the array type.
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 and places NULL values at the end of the array. The syntax is as follows:
array_sort(arr)
The input parameter is described as follows:
arr: specifies an 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
SELECT array_sort([NULL,1,2,NULL,2,NULL,NULL]);
+-----------------------------------------+
| array_sort([NULL,1,2,NULL,2,NULL,NULL]) |
+-----------------------------------------+
| [1,2,2,NULL,NULL,NULL,NULL] |
+-----------------------------------------+
1 row in set
SELECT array_sort(["hello","hello",NULL,NULL,"OceanBase"]);
+-----------------------------------------------------+
| array_sort(["hello","hello",NULL,NULL,"OceanBase"]) |
+-----------------------------------------------------+
| ["OceanBase","hello","hello",NULL,NULL] |
+-----------------------------------------------------+
1 row in set
array_length
The array_length() function returns the length of an array. If the array is nested, it returns the length of the outermost array. The syntax is as follows:
array_length(arr)
The input parameter is described as follows:
arr: specifies an array value. It supports nested arrays.
The return value is of the UINT32 type.
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 numbers. 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. The default value is0.end: specifies the ending value. It can be of only the integer type.step: specifies the step size. It can be of only the integer type. The default value is1.
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, it returns an empty array.
Specific details are as follows:
- Generated array:
array_rangegenerates an array of consecutive integers that includes thestartelement but excludes theendelement. This means the generated array starts with thestartvalue and ends with theendvalue, but theendvalue itself is not included in the result. - Step size: If a step size is provided (step), each element in the generated array will be
stepgreater than the previous element. For example, ifstartis1,endis10, andstepis2, 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 ifendis less than or equal tostart, or if the step sizestepis zero or negative, the generated array will have no valid elements, and thus, it returns an empty array.
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 value of the numeric element type.- Handling of
NULLvalues:- If all elements in the array are
NULL, it returnsNULL. - If not all elements are
NULL, it treatsNULLvalues as0for the calculation.
- If all elements in the array are
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 the integer (INT, BIGINT, etc.) or float (FLOAT, DOUBLE) type.
- 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 two adjacent elements in an array and stores the result in a new array, which is then returned. The syntax is as follows:
array_difference(arr)
The input parameter is described as follows:
arr: specifies an array value.
The return value is of the array type.
Functional constraints are as follows:
- The array elements must be of the integer (INT, BIGINT, etc.) or float (FLOAT, DOUBLE) type.
- Nested arrays are not supported.
Specific details are as follows:
- Calculation method:
- The first element in the returned array is fixed to
0. - The value of the nth element in the returned array is
array[n] - array[n-1], which is the current element minus the previous element.
- The first element in the returned array is fixed to
- Return value:
- A new array where the first element is
0and the subsequent elements are the differences between adjacent elements.
- A new array where the first element is
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 value.- Handling of
NULLvalues:- If all elements in the array are
NULL, it returnsNULL. - If not all elements are
NULL, it skips theNULLelements.
- If all elements in the array are
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
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 value.- Handling of
NULLvalues:- If all elements in the array are
NULL, it returnsNULL. - If not all elements are
NULL, it skips theNULLelements.
- If all elements in the array are
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_max([1,2,4]);
+--------------------+
| array_max([1,2,4]) |
+--------------------+
| 4 |
+--------------------+
1 row in set
SELECT array_max([1.1,2.2,4.4]);
+--------------------------+
| array_max([1.1,2.2,4.4]) |
+--------------------------+
| 4.4 |
+--------------------------+
1 row in set
SELECT array_max([1,2,NULL,3]);
+-------------------------+
| array_max([1,2,NULL,3]) |
+-------------------------+
| 3 |
+-------------------------+
1 row in set
array_avg
The array_avg() function returns the average of all elements in the array. The syntax is as follows:
array_avg(arr)
The input parameters are described as follows:
arrmust be an array type.- Handling of
NULLvalues:- If all elements in the array are
NULL, the function returnsNULL. - If not all elements are
NULL, theNULLvalues are treated as0during the calculation.
- If all elements in the array are
The return value is an array of DOUBLE type.
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_avg([1,2,-4]);
+---------------------+
| array_avg([1,2,-4]) |
+---------------------+
| -0.3333333333333333 |
+---------------------+
1 row in set
SELECT array_avg([1,2,NULL,3]);
+-------------------------+
| array_avg([1,2,NULL,3]) |
+-------------------------+
| 1.5 |
+-------------------------+
1 row in set
array_position
The array_position() function finds the position of a specified element in the array. The syntax is as follows:
array_position(arr, element)
The input parameters are described as follows:
arrmust be an array type.elementis the element to find, which can be a basic type or an array type supported by the array.
The return value is an array of INT type. If the element does not exist, 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 the array starting from a specified position and returns the extracted elements as a new array. The syntax is as follows:
array_slice(arr, offset, length)
The input parameters are described below:
arrmust be an array type.offsetspecifies the starting element position:- A positive value indicates the
offsetth element from the left. - A negative value indicates the
-offsetth element from the right.
- A positive value indicates the
lengthspecifies the maximum number of elements to extract (optional parameter):- A positive value indicates the maximum number of elements to extract.
- A negative value indicates the right boundary to extract to, i.e., the
-lengthth element from the right. - If not provided, all elements starting from
offsetare extracted.
The returned value is described below:
- The returned value is of array type.
- When
offsetis greater than the array length oroffsetis 0, an empty array is returned.
Examples are as follows:
-- offset is a positive value, indicating the 2nd element from the left, starting the extraction 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 value, indicating the 10th element from the left. However, since the offset exceeds the array length, an empty array is returned.
SELECT array_slice([1,2,3,4,5,6,7,8,9],10);
+-------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],10) |
+-------------------------------------+
| [] |
+-------------------------------------+
1 row in set
-- offset is 0, an empty array is returned.
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 value, indicating the 2nd element from the right, starting the extraction 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 value, indicating the extraction of 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 value, indicating the extraction of 10 elements beyond 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 that the last two elements are not extracted, i.e., 8 and 9 are not included.
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 that the last 10 elements are not extracted. In this case, it exceeds the left boundary and returns an empty array.
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 that the extraction starts from the 10th element from the right. However, it exceeds the left boundary by 1 element and extracts 4 elements from that position.
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, which means to start extracting from the 6th position from the right, which is 4; length is -4, which means to skip 4 elements from the right, which are 6, 7, 8, and 9.
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. Syntax:
reverse(arr)
The input parameters are described as follows:
arrThe input must be an array type. Nested arrays are supported.
The return value is an array.
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 applies a lambda function to each element of the input arrays and returns a new array containing the transformed elements. Syntax:
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 thedefstatement. Instead, it is created using thelambdakeyword. A lambda function can accept any number of parameters but must contain only one expression.arr1,...,arrnThe input arrays. The input must be an array type, and the number of elements in each array must be the same. The value ofnmust match the number of parameters in the lambda function.- For each input array, the i-th element is taken as the input parameter for the lambda function, and the return value becomes the i-th element of the new array.
The return value is an array. The element type of the array is the return type of the lambda function, and the array length is the number of elements in the input array.
The function has the following constraints:
- The
expressionin the lambda function does not support subqueries. - The
expressionin the lambda function does not support aggregate functions. - The
expressionin the lambda function does not support expressions for generated columns.
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 the 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. Syntax:
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 thedefstatement. Instead, it is created using thelambdakeyword. A lambda function can accept any number of parameters but must contain only one expression.arr1,...,arrnThe input arrays. The input must be an array type, and the number of elements in each array must be the same. The value ofnmust match the number of parameters in the lambda function. Nested arrays are supported.
The return value is an array, which is the filtered arr1 array.
The function has the following constraints:
- Only supports return values of integer or
NULLfor the lambda function.
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. Syntax:
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 thedefstatement. Instead, it is created using thelambdakeyword. A lambda function can accept any number of parameters but must contain only one expression.arr1,...,arrnThe input arrays. The input must be an array type, and the number of elements in each array must be the same. The value ofnmust match the number of parameters in the lambda function. Nested arrays are supported.
The return value is an array.
The function has the following constraints:
- Only supports return values of basic element types supported by arrays for the lambda function, not array types.
The specific working process is as follows:
- Generate the sorting basis: First,
array_sortbycalculates each element of the input array based on the provided lambda function and generates a new array containing the results of each element processed by the lambda function. - Ascending sort and handle
NULLvalues: Next, the new array is sorted in ascending order, withNULLvalues placed at the end. This means that during sorting, all non-NULLvalues are prioritized, andNULLvalues are placed at the end of the sorted result. - Re-sort the original array based on the sorting order: Finally,
array_sortbysorts the original array based on the sorting order of the new array. This means that the elements of the original array are arranged in the order of the sorting results of the lambda function. - Return the sorted array: Ultimately, the function returns the sorted original array.
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
array_first() returns the first element in arr1 for which the given lambda function returns a value other than 0. Syntax:
array_first((x1,...,xn) -> (expression), array1,...,arrayn)
The input parameters are described as follows:
(x1,...,xn) -> (expression)specifies a lambda function.arr1,...,arrnspecifies the input arrays. The input must be an array type, and the number of elements in each array must be the same.nmust match the number of parameters in the lambda function. Nested arrays are supported.
The return value is of any element type.
The function is described as follows:
- Lambda function: You must provide a lambda function that evaluates each element in the array. The lambda function must return an integer.
- Nested arrays: The
array_firstfunction supports nested arrays, meaning 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 than0, and returns that element as the result. If no such element exists, it may returnNULL.
Here are some examples:
SELECT array_first(x ->(x + 1 > 2),[1,2,3,4]);
+----------------------------------------+
| array_first(x ->(x + 1 > 2),[1,2,3,4]) |
+----------------------------------------+
| 2 |
+----------------------------------------+
1 row in set
SELECT array_first((x,y) ->(y),[[1],[2],[3]], [0,1,3]);
+-------------------------------------------------+
| array_first((x,y) ->(y),[[1],[2],[3]], [0,1,3]) |
+-------------------------------------------------+
| [2] |
+-------------------------------------------------+
1 row in set
The examples are described as follows:
- Lambda function:
(x,y) -> (y)is a lambda function that accepts two parameters,xandy, and returns the value ofy. In this specific call,xis not actually used. - Input arrays:
[[1],[2],[3]]is a nested array containing three subarrays.[0,1,3]is a one-dimensional array containing three integers.
- Return value calculation:
- The
array_firstfunction traverses the elements in the input array[[1],[2],[3]], calls the lambda function for each element, and evaluates it using the correspondingyvalue from[0,1,3]. - In this example, the lambda function returns
0,1, and3, corresponding to[1],[2], and[3], respectively. - Therefore,
array_firstfinds the first element in[[1],[2],[3]]for which the lambda function returns a value other than0. In this case, theyvalue for[2]is1, which is the first non-zero value. The final return value is[2].
- The
array_except
array_except() returns an array containing elements that are present in arr1 but not in arr2. Syntax:
array_except(arr1, arr2)
The input parameters are described as follows:
arr1: the left array to calculate.arr2: the right array to calculate.
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, the elements involved in the operation are the first-level arrays. Even if the elements of two arrays are the same but in different orders, they are considered different arrays.
NULLvalue handling: If any element in the array isNULL, the result isNULL.
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
array_intersect() 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 to calculate the intersection.
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, the elements involved in the operation are the first-level arrays. Even if the elements of two arrays are the same but in different orders, they are considered different arrays.
NULLvalue handling: 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
array_union() 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 to calculate the union.
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, the elements involved in the operation are the first-level arrays. Even if the elements of two arrays are the same but in different orders, they are considered different arrays.
NULLvalue handling: 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