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:
- When
arr1is a base array,elementmust be a base type supported by arrays, such as Tinyint, Smallint, Int, Bigint, Float, Double, or VarChar. - When
arr1is a nested array,elementmust be an array with the same number of nesting 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 (such as 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:
- When
arr1is a base array,elementmust be a base type supported by arrays, such as Tinyint, Smallint, Int, Bigint, Float, Double, or VarChar. - When
arr1is a nested array,elementmust be an array with the same number of nesting 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 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.indexis the position of the subelement to be retrieved. 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 in sequence. 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, such as Char or Varchar.delimiteris the delimiter. It must be a character type, such as Char or Varchar.null_str(optional) is the null string. It must be a character type, such as Char or Varchar.
The return value is an array whose elements are characters.
Here are some examples:
SELECT string_to_array('1and2and3and', 'and');
+----------------------------------------+
| string_to_array('1and2and3and', 'and') |
+----------------------------------------+
| ["1","2","3",""] |
+----------------------------------------+
1 row in set
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)
Parameters
Both
stringanddelimiterareVARCHARtypes.The return value is of the
ARRAY<varchar>type.
Here is an example:
SELECT split('1#2#3', '#'), split('#1#2#3#', '#'), split('123', '#');
+---------------------+-----------------------+-------------------+
| split('1#2#3', '#') | split('#1#2#3#', '#') | split('123', '#') |
+---------------------+-----------------------+-------------------+
| ["1","2","3"] | ["","1","2","3",""] | ["123"] |
+---------------------+-----------------------+-------------------+
contains
The contains() function checks whether a specified element exists in a specified container or string and returns a Boolean value. The syntax is as follows:
CONTAINS(array_expr, value)
Examples are shown below:
SELECT CONTAINS([1, 2, 3, 4, 5], 3), CONTAINS([1, 2, 3, 4, 5], 6), CONTAINS(['a', 'b', 'c'], 'b'), CONTAINS(['a', 'b', 'c'], 'd');
+------------------------------+------------------------------+--------------------------------+--------------------------------+
| CONTAINS([1, 2, 3, 4, 5], 3) | CONTAINS([1, 2, 3, 4, 5], 6) | CONTAINS(['a', 'b', 'c'], 'b') | CONTAINS(['a', 'b', 'c'], 'd') |
+------------------------------+------------------------------+--------------------------------+--------------------------------+
| 1 | 0 | 1 | 0 |
+------------------------------+------------------------------+--------------------------------+--------------------------------+
array_agg
The array_agg() function aggregates values from multiple rows into a single array based on the specified column and returns the result. The syntax is as follows:
array_agg([DISTINCT] col [ORDER BY col0 [DESC | ASC] [NULLS FIRST | NULLS LAST] ...])
The following table lists the input parameters.
colspecifies the column to aggregate data for.DISTINCT(optional) specifies whether to deduplicate thecolcolumn.ORDER BY(optional) specifies whether thecolcolumn is to be sorted.DESC(optional). Specifies to sort in descending order.ASC(optional) specifies ascending order.NULLS FIRST(optional) specifies thatNULLvalues are placed at the beginning during sorting.NULLS LAST(optional) specifies thatNULLvalues are listed last during sorting.
The return value is of the array type.
The procedure must meet the following conditions:
ORDER BYclause specifiescol0with an array type.- The
ORDER BYclause does not support specifying sort columns by numbers such as1, 2, .... If numbers are used, theORDER BYclause will be ignored.
Examples:
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 of an array into multiple rows and returns a relational table containing these elements. The syntax is as follows:
unnest(arr_list) [AS] [table_name[(col_name_list)]]
The following table describes the input parameters:
arr_listspecifies one or more array values, separated by commas.table_name(optional) specifies the name of the table to be returned, with a default value ofunnest. TheASkeyword is optional and does not change the functionality.col_name_list(optional) specifies the names of the columns that are returned. The default value isunnest, and the names are separated by commas. The number of input column names must be the same as the number of values in the array.
The return value is described as follows:
- Returns a table with a number of rows equal to the maximum number of elements in the input arrays and a number of columns equal to the number of input arrays. Columns with fewer array elements are padded with
NULLvalues for the empty rows. - For an array that is nested n layers deep,
unnestonly unfolds the first layer (the outermost layer), and the output is an array nested n-1 layers deep. - The content of the nth line corresponds to the nth subelement of each array. If the elements of
arr_listare basic data types, the output type is the corresponding type of those elements. If the elements are array types, the output type is the array type.
The following restrictions apply:
- The
unnest()function can only be placed in theFROMclause.
The following example shows how to call the DELETE_SCHEMA_STATS 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 prepends an element to an array and returns the new array. Syntax:
array_prepend(array, element)
The following table describes the input parameters:
arrayAn array value.elementspecifies the element to be added to the array, and its type must be the same as the array element type specified by thearrayparameter.
The return type is an array.
Examples The following example shows how to drop schema statistics in a session with schema privileges. The following example shows how to drop schema statistics in a session with schema privileges.
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 one array and returns the resulting array. Syntax:
array_concat(array_list)
The parameters are described as follows:
array_listspecifies one or more array values.
The return value is of an array type.
Constraints:
- Nested array concatenation is supported only if all arrays have the same depth. The concatenation result includes elements from the outermost level.
The following is an example of how to use the DELETE_SCHEMA_STATS 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. Syntax:
array_compact(array)
The input parameter is described as follows:
array: specifies an array value.
The return value is of the array type.
Here are some examples:
SELECT array_compact([1,2,2,3,3,2]);
+------------------------------+
| array_compact([1,2,2,3,3,2]) |
+------------------------------+
| [1,2,3,2] |
+------------------------------+
1 row in set
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. Syntax:
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 first-level (outermost) array. Syntax:
array_length(arr)
The input parameter is described as follows:
arr: specifies an array value. Nested arrays are supported.
The return value is of the unit32 unsigned integer type.
Here are some examples:
SELECT array_length([1,2,3]);
+-----------------------+
| array_length([1,2,3]) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set
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. Syntax:
array_range(end)
array_range(start, end)
array_range(start, end, step)
The input parameters are described as follows:
start: the starting value, with a default value of0.end: the ending value, which must be an integer.step: the step size, which must be an integer, with a default value of1.
The return value is an array of BIGINT elements. The output includes the input start value but excludes the end value. If (end - start) / step <= 0, an empty array is returned.
Specific details are as follows:
- Generated array:
array_rangegenerates an array containing consecutive integers, including thestartelement but excluding theendelement. This means the generated array starts atstartand ends atend, butenditself is not included in the result. - Step size: If a step parameter (step) is provided, 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: When
(end - start)/step <= 0, the function returns an empty array. This means that ifendis less than or equal tostart, or if the stepstepis zero or negative, the generated array will have no valid elements, and thus an empty array is returned.
Here are some examples:
SELECT array_range(5);
+----------------+
| array_range(5) |
+----------------+
| [0,1,2,3,4] |
+----------------+
1 row in set
SELECT array_range(-1,4);
+-------------------+
| array_range(-1,4) |
+-------------------+
| [-1,0,1,2,3] |
+-------------------+
1 row in set
SELECT array_range(-1,4,2);
+---------------------+
| array_range(-1,4,2) |
+---------------------+
| [-1,1,3] |
+---------------------+
1 row in set
array_sum
The array_sum() function calculates the sum of all elements in an array. Syntax:
array_sum(arr)
The input parameters are described as follows:
arr: specifies an array value of numeric elements.- Handling of
NULLvalues:- If all elements in the array are
NULL,NULLis returned. - If not all elements are
NULL,NULLvalues are treated as0in the calculation.
- If all elements in the array are
The return value is described as follows:
- An array of integer (INT) elements.
- Or an array of floating-point (DOUBLE) elements.
Functional constraints are as follows:
- The array elements must be of integer (INT, BIGINT, etc.) or floating-point (FLOAT, DOUBLE) numeric types.
- 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. Syntax:
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 integer (INT, BIGINT, etc.) or floating-point (FLOAT, DOUBLE) numeric types.
- Nested arrays are not supported.
Specific details are as follows:
- Calculation method:
- The first element of the returned array is fixed at
0. - The value of the nth element in the returned array is
array[n] - array[n-1], which is the current element minus the previous element.
- The first element of the returned array is fixed at
- Return value:
- A new array is returned, where the first element is
0and the subsequent elements are the differences between adjacent elements.
- A new array is returned, 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. Syntax:
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,NULLis returned. - If not all elements are
NULL,NULLvalues are skipped.
- 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:
arrmust be an array.- Handling of
NULLvalues:- If all elements in the array are
NULL,NULLis returned. - If not all elements are
NULL,NULLvalues are skipped.
- If all elements in the array are
The return value is of the same type as the elements in the input array.
The following limitations apply:
- Nested arrays are not supported.
Here are some examples:
SELECT array_max([1,2,4]);
+--------------------+
| array_max([1,2,4]) |
+--------------------+
| 4 |
+--------------------+
1 row in set
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 value of all elements in an array. The syntax is as follows:
array_avg(arr)
The input parameters are described as follows:
arrmust be an array.- Handling of
NULLvalues:- If all elements in the array are
NULL,NULLis returned. - If not all elements are
NULL,NULLvalues are treated as0during the calculation.
- If all elements in the array are
The return value is of the DOUBLE type.
The following limitations apply:
- Only INT and DOUBLE values are supported as array elements.
- 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 returns the position of a specified element in an array. The syntax is as follows:
array_position(arr, element)
The input parameters are described as follows:
arrmust be an array.elementis the element to be found. It can be a basic type or an array type supported by the array.
The return value is of the INT type. If the element does not exist, 0 is returned.
Here are some examples:
SELECT array_position([1,2,3], 2);
+--------------------------------+
| array_position([1,2,3], 2) |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set
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 an array starting at 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 as follows:
arrmust be an array type.offsetspecifies the starting position for extraction:- 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 length limit (optional parameter):- A positive value indicates the maximum number of elements to extract.
- A negative value indicates the right boundary for extraction, i.e., the
-lengthth element from the right. - If not specified, all elements from
offsetare extracted.
The return value is described as follows:
- The return value is of array type.
- If
offset>array lengthoroffset=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 for extraction
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 greater than the array length, returning an empty array
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 for extraction
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 extracting 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 extracting 10 elements, exceeding 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 not extracting 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 not extracting the last 10 elements, which exceeds the left boundary, returning 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 starting extraction from the 10th element from the right, which exceeds the left boundary by 1, extracting length (4) elements from this 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 extract from the 6th position from the right, that is, 4; length is -4, which means to skip 4 elements from the right, that is, 6,7,8,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 in an array. If the array is nested, it reverses the elements in the innermost array. The syntax is as follows:
reverse(arr)
The input parameters are described as follows:
arrThe input type must be an array. Nested arrays are supported.
The return value is an array.
Here are some examples:
SELECT reverse([1,2,3]);
+----------------+
| reverse([1,2,3]) |
+----------------+
| [3,2,1] |
+----------------+
1 row in set
SELECT reverse([['a'], ['b'], ['c']]) ;
+--------------------------------+
| reverse([['a'], ['b'], ['c']]) |
+--------------------------------+
| [["c"],["b"],["a"]] |
+--------------------------------+
1 row in set
array_map
The array_map() function applies a Lambda function to each element of the input array and returns a new array containing the results. 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 thedefstatement. Instead, it is created using thelambdakeyword. A Lambda function can accept any number of parameters but can only contain a single expression.arr1,...,arrnThe input arrays. The input type must be an array, 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 an 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 same as the number of elements in the input array.
The following constraints apply to this function:
- 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.
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 from the arr1 array based on the return value of a Lambda function. If the Lambda function returns 0 or NULL, the corresponding element from arr1 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 thedefstatement. Instead, it is created using thelambdakeyword. A Lambda function can accept any number of parameters but can only contain a single expression.arr1,...,arrnThe input arrays. The input type must be an array, 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 following constraints apply to this function:
- Only integer or
NULLvalues are supported as return values from the Lambda function.
Here are some examples:
SELECT array_filter(x ->(x + 1 > 2),[1,2,3,4]);
+-----------------------------------------+
| array_filter(x ->(x + 1 > 2),[1,2,3,4]) |
+-----------------------------------------+
| [2,3,4] |
+-----------------------------------------+
1 row in set
SELECT array_filter((x, y) ->(y), [1,2,3,4,5], [NULL,1,-1,0,2]);
+----------------------------------------------------------+
| array_filter((x, y) ->(y), [1,2,3,4,5], [NULL,1,-1,0,2]) |
+----------------------------------------------------------+
| [2,3,5] |
+----------------------------------------------------------+
1 row in set
SELECT array_filter((x, y) ->(y), [['a'],['b','c'],['d']], [1,0,1]);
+--------------------------------------------------------------+
| array_filter((x, y) ->(y), [['a'],['b','c'],['d']], [1,0,1]) |
+--------------------------------------------------------------+
| [["a"],["d"]] |
+--------------------------------------------------------------+
1 row in set
array_sortby
The array_sortby() function sorts an array based on a given Lambda function. The syntax is as follows:
array_sortby((x1,...,xn) -> (expression), arr1,...,arrn)
The input parameters are described as follows:
(x1,...,xn) -> (expression)A Lambda function. A Lambda function is an anonymous function that does not require the use of thedefstatement. Instead, it is created using thelambdakeyword. A Lambda function can accept any number of parameters but can only contain a single expression.arr1,...,arrnThe input arrays. The input type must be an array, 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 following constraints apply to this function:
- Only the base element types supported by arrays are supported as return values from the Lambda function. Array types are not supported.
The specific working process is as follows:
- Generate the sorting criteria: First,
array_sortbycalculates the result of the Lambda function for each element in the input array and generates a new array containing these results. - Sort in ascending order and handle
NULLvalues: Next, the new array is sorted in ascending order, withNULLvalues placed at the end. This means that all non-NULLvalues are prioritized during sorting, whileNULLvalues are placed at the end of the sorted result. - Reorder the original array based on the sorting criteria: Finally,
array_sortbyreorders the original array based on the sorting order of the new array. This means that the elements in the original array are rearranged according to the sorting order of the Lambda function's results. - Return the sorted array: Ultimately, 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,...,arrnspecifies the input arrays. The input type must be an array type, and the number of elements in the arrays 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.
Specific details are as follows:
- Lambda function: You must provide a Lambda function that evaluates each element in the array. The return value of the Lambda function must be an integer.
- Nested arrays: The
array_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 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 each element in the input array[[1],[2],[3]]and calls the Lambda function for each element, using the correspondingyvalue (from[0,1,3]) for evaluation. - 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, the first non-zero value. The final return value is[2].
- The
array_except
The array_except() function returns an array of elements that are present in arr1 but not in arr2. The syntax is as follows:
array_except(arr1, arr2)
The input parameters are described as follows:
arr1: the left array to be processed.arr2: the right array to be processed.
The return value is of array type.
Specific details are 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 at the first level are used for the operation. Even if two arrays have the same elements but in different orders, they are considered different arrays.
- Handling of
NULLvalues: 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
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 for which the intersection is to be calculated.
The return value is of array type.
Specific details are 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 at the first level are used for the operation. Even if two arrays have the same elements but in different orders, they are considered different arrays.
- Handling of
NULLvalues: 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 for which the union is to be calculated.
The return value is of array type.
Specific details are 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 at the first level are used for the operation. Even if two arrays have the same elements but in different orders, they are considered different arrays.
- Handling of
NULLvalues: 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