Array manipulation functions perform basic operations on array data, such as adding elements, removing elements, and searching for elements. They return the array data after the operations. OceanBase Database supports the following array manipulation functions: array_append(), array_distinct(), array_remove(), cardinality(), element_at(), string_to_array(), array_agg(), and unnest().
array_append
The array_append() function appends a specified element to a target array. The syntax is as follows:
array_append(arr1, element)
The parameters are described as follows:
- If
arr1is a base array,elementmust be a basic data type supported by arrays, such as Tinyint, Smallint, Int, Bigint, Float, Double, or VarChar. - If
arr1is a nested array,elementmust be an array whose nesting level is the same as that of the subarrays ofarr1.
The return value is of the array type.
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 duplicates from a target array. The syntax is as follows:
array_distinct(arr1)
The parameters are described as follows:
arr1must be of an array type.
The return value is of the array type.
If the data types of the elements in the array are inconsistent (for example, the elements are of string and numeric types), an error is returned.
Here are some examples:
SELECT array_distinct([1,2,3,2,3]);
+-----------------------------+
| array_distinct([1,2,3,2,3]) |
+-----------------------------+
| [1,2,3] |
+-----------------------------+
1 row in set
SELECT array_distinct([null,2,3,null]);
+-----------------------------+
| array_distinct([null,2,3,null]) |
+---------------------------------+
| [NULL,2,3] |
+---------------------------------+
1 row in set
SELECT array_distinct([1,2,3,2.0]);
+-----------------------------+
| array_distinct([1,2,3,2.0]) |
+-----------------------------+
| [1,2,3] |
+-----------------------------+
1 row in set
SELECT array_distinct([1.1,2.2,3.3,2.2]);
+-------------------------------+
| array_distinct([1.1,2.2,3.3,2.2]) |
+-----------------------------------+
| [1.1,2.2,3.3] |
+-----------------------------------+
1 row in set
SELECT array_distinct(["hello", "hi", "hi"]);
+---------------------------------------+
| array_distinct(["hello", "hi", "hi"]) |
+---------------------------------------+
| ["hello","hi"] |
+---------------------------------------+
1 row in set
SELECT array_distinct([[1,2],[3,4], [3,4]]);
+--------------------------------------+
| array_distinct([[1,2],[3,4], [3,4]]) |
+--------------------------------------+
| [[1,2],[3,4]] |
+--------------------------------------+
1 row in set
SELECT array_distinct([["hello", "world"], ["hi", "what"], ["are you?"], ["are you?"]]);
+----------------------------------------------------------------------------------+
| array_distinct([["hello", "world"], ["hi", "what"], ["are you?"], ["are you?"]]) |
+----------------------------------------------------------------------------------+
| [["hello","world"],["hi","what"],["are you?"]] |
+----------------------------------------------------------------------------------+
1 row in set
array_remove
The array_remove() function removes a specified element from a target array. The syntax is as follows:
array_remove(arr1, element)
The parameters are described as follows:
- If
arr1is a base array,elementmust be a basic data type supported by arrays, such as Tinyint, Smallint, Int, Bigint, Float, Double, or VarChar. - If
arr1is a nested array,elementmust be an array whose nesting level is the same as that of the subarrays ofarr1.
The return value is of the array type.
Here are some examples:
SELECT array_remove([1,2,3], 2);
+--------------------------+
| array_remove([1,2,3], 2) |
+--------------------------+
| [1,3] |
+--------------------------+
1 row in set
SELECT array_remove([1,2,3], 2.0);
+----------------------------+
| array_remove([1,2,3], 2.0) |
+----------------------------+
| [1,3] |
+----------------------------+
1 row in set
SELECT array_remove([1.1,2.2,3.3], 2.2);
+----------------------------------+
| array_remove([1.1,2.2,3.3], 2.2) |
+----------------------------------+
| [1.1,3.3] |
+----------------------------------+
1 row in set
SELECT array_remove(["hello", "hi"], "hi");
+-------------------------------------+
| array_remove(["hello", "hi"], "hi") |
+-------------------------------------+
| ["hello"] |
+-------------------------------------+
1 row in set
SELECT array_remove([[1,2],[3,4]], [3,4]);
+------------------------------------+
| array_remove([[1,2],[3,4]], [3,4]) |
+------------------------------------+
| [[1,2]] |
+------------------------------------+
1 row in set
SELECT array_remove([[1,2],[3,4]], [3.0,4.0]);
+----------------------------------------+
| array_remove([[1,2],[3,4]], [3.0,4.0]) |
+----------------------------------------+
| [[1,2]] |
+----------------------------------------+
1 row in set
SELECT array_remove([["hello", "world"], ["hi", "what"], ["are you?"]], ["are you?"]);
+--------------------------------------------------------------------------------+
| array_remove([["hello", "world"], ["hi", "what"], ["are you?"]], ["are you?"]) |
+--------------------------------------------------------------------------------+
| [["hello","world"],["hi","what"]] |
+--------------------------------------------------------------------------------+
1 row in set
cardinality
The cardinality() function returns the number of base elements in a target array. If the target array is a nested array, the function returns the total number of base elements in all non-empty subarrays. The syntax is as follows:
cardinality(arr1)
The parameters are described as follows:
arr1must be of an array type.
The return value is of the integer type.
Here are some examples:
SELECT cardinality([1,2,3]);
+----------------------+
| cardinality([1,2,3]) |
+----------------------+
| 3 |
+----------------------+
1 row in set
SELECT cardinality([1,2,3,NULL]);
+----------------------------+
| cardinality([1,2,3,NULL]) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set
SELECT cardinality(['a','b','c','d']);
+----------------------------+
| cardinality(['a','b','c','d']) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set
SELECT cardinality([[1,2,3],[4]]);
+----------------------------+
| cardinality([[1,2,3],[4]]) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set
SELECT cardinality([['a','b',NULL,'c'],[NULL,'d']]);
+----------------------------------------------+
| cardinality([['a','b',NULL,'c'],[NULL,'d']]) |
+----------------------------------------------+
| 6 |
+----------------------------------------------+
1 row in set
SELECT cardinality([[1,2,3],NULL]);
+-----------------------------+
| cardinality([[1,2,3],NULL]) |
+-----------------------------+
| 3 |
+-----------------------------+
1 row in set
element_at
The element_at() function returns the element at a specified position in a target array based on the index. The syntax is as follows:
element_at(arr1, index)
The parameters are described as follows:
arr1must be of an array type.indexspecifies the position of the subelement to be obtained. It must be of the integer type.
The return value is described as follows:
- If
arr1is a nested array, the return value is of the array type. - If
arr1is a base type, the return value is of 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 characters to an array. Specifically, it splits the input characters into multiple elements based on the specified delimiter and null element, and then places the elements in an array. The delimiter and null element are case-sensitive. The syntax is as follows:
string_to_array(arr_str, delimiter[, null_str])
The parameters are described as follows:
arr_strmust be of a character type, such as Char or Varchar.delimiterspecifies the delimiter. It must be of a character type, such as Char or Varchar.null_str(optional) specifies the null element. It must be of a character type, such as Char or Varchar.
The return value is of the array type whose elements are of character type.
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
array_agg
The array_agg() function aggregates multiple rows of data in a specified column into an 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 parameters are described as follows:
colspecifies the target column for aggregation.DISTINCT(optional) specifies whether to remove duplicates from thecolcolumn.ORDER BY(optional) specifies whether to sort thecolcolumn.DESC(optional) specifies the sorting direction as descending.ASC(optional) specifies the sorting direction as ascending.NULLS FIRST(optional) specifies to placeNULLvalues at the beginning of the sort.NULLS LAST(optional) specifies to placeNULLvalues at the end of the sort.
The return value is of the array type.
The following constraints apply:
- The
col0specified inORDER BYcannot be of the array type. ORDER BYdoes not support specifying the column to be sorted by using1, 2, .... If you specify a number,ORDER BYis 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 | Physics | 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 of these elements. The syntax is as follows:
unnest(arr_list) [AS] [table_name[(col_name_list)]]
The parameters are described as follows:
arr_listspecifies one or more array values separated by commas.table_name(optional) specifies the name of the returned table. The default value isunnest. TheASkeyword can be omitted without changing the function.col_name_list(optional) specifies the column names of the returned columns separated by commas. The default value isunnest. The number of input column names must be the same as the number of array values.
The return value is described as follows:
- A table is returned, with the number of rows equal to the maximum number of elements in the input arrays, the number of columns equal to the number of input arrays, and empty rows in columns with fewer elements filled with
NULL. - For an n-level nested array,
unnestonly unpacks the first layer (the outermost array), resulting in an output that is an [n-1]-level nested array. - The nth row contains the nth subelement of each array. If the elements of
arr_listare basic data types, the output type is the same as the type of the corresponding element. If the elements are arrays, the output type is also an array.
The following constraints apply:
- The
unnest()function can only be used after theFROMclause.
Here are some examples:
SELECT * FROM unnest([1,2,3]);
+--------+
| unnest |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
3 rows in set
SELECT * FROM unnest([1,2,NULL,3], ['11',NULL,'22']);
+--------+--------+
| unnest | unnest |
+--------+--------+
| 1 | 11 |
| 2 | NULL |
| NULL | 22 |
| 3 | NULL |
+--------+--------+
4 rows in set
SELECT t.* FROM unnest([[1,2],[3],NULL,[4,5,6]]) AS t;
+---------+
| unnest |
+---------+
| [1,2] |
| [3] |
| NULL |
| [4,5,6] |
+---------+
4 rows in set
SELECT t.* FROM unnest([[1,2],[3],NULL,[4,5,6]], ['hi','hello']) AS t(c1,c2);
+---------+-------+
| c1 | c2 |
+---------+-------+
| [1,2] | hi |
| [3] | hello |
| NULL | NULL |
| [4,5,6] | NULL |
+---------+-------+
4 rows in set
SELECT * FROM unnest([1,2,3]) t1(c1), unnest(['11','22']) AS t2(c2);
+------+------+
| c1 | c2 |
+------+------+
| 1 | 11 |
| 1 | 22 |
| 2 | 11 |
| 2 | 22 |
| 3 | 11 |
| 3 | 22 |
+------+------+
6 rows in set
array_prepend
The array_prepend() function adds an element to the beginning of an array and returns a new array. The syntax is as follows:
array_prepend(array, element)
The parameters are described as follows:
array: an array.element: the target element to be added. It must be of the same type as the elements in thearrayparameter.
The return value is of the array type.
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 and returns a new array. The syntax is as follows:
array_concat(array_list)
The parameters are described as follows:
array_list: a list of arrays to be merged. It can be one or more arrays.
The return value is of the array type.
Note that:
- Nested arrays can be merged. However, all arrays must have the same nesting level. The return result is an array whose elements are the merged arrays at the first level (namely, the outermost level).
Here is an example:
SELECT array_concat([1,2,3], [4,5,6]);
+--------------------------------+
| array_concat([1,2,3], [4,5,6]) |
+--------------------------------+
| [1,2,3,4,5,6] |
+--------------------------------+
1 row in set
SELECT array_concat([1,2,3], [-4], [5.5,6]);
+--------------------------------------+
| array_concat([1,2,3], [-4], [5.5,6]) |
+--------------------------------------+
| [1,2,3,-4,5.5,6] |
+--------------------------------------+
1 row in set
SELECT array_concat([[1,2,3]],[[11],[22,44]]);
+----------------------------------------+
| array_concat([[1,2,3]],[[11],[22,44]]) |
+----------------------------------------+
| [[1,2,3],[11],[22,44]] |
+----------------------------------------+
1 row in set
array_compact
The array_compact() function deletes consecutive duplicate elements in an array and returns a new array. The syntax is as follows:
array_compact(array)
The parameters are described as follows:
array: an array.
The return value is of the array type.
Here is an example:
SELECT array_compact([1,2,2,3,3,2]);
+------------------------------+
| array_compact([1,2,2,3,3,2]) |
+------------------------------+
| [1,2,3,2] |
+------------------------------+
1 row in set
SELECT array_compact(["hello","hello",NULL,NULL,"OceanBase"]);
+--------------------------------------------------------+
| array_compact(["hello","hello",NULL,NULL,"OceanBase"]) |
+--------------------------------------------------------+
| ["hello",NULL,"OceanBase"] |
+--------------------------------------------------------+
1 row in set
SELECT array_compact([[1,2,3,NULL],[4,NULL],[4,NULL]]);
+-------------------------------------------------+
| array_compact([[1,2,3,NULL],[4,NULL],[4,NULL]]) |
+-------------------------------------------------+
| [[1,2,3,NULL],[4,NULL]] |
+-------------------------------------------------+
1 row in set
array_sort
The array_sort() function sorts an array in ascending order and places NULL values at the end of the array. The syntax is as follows:
array_sort(arr)
The parameters are described as follows:
arr: an array.
The return value is of the array type.
Here is an example:
SELECT array_sort([2,1,3]);
+---------------------+
| array_sort([2,1,3]) |
+---------------------+
| [1,2,3] |
+---------------------+
1 row in set
SELECT array_sort([NULL,1,2,NULL,2,NULL,NULL]);
+-----------------------------------------+
| array_sort([NULL,1,2,NULL,2,NULL,NULL]) |
+-----------------------------------------+
| [1,2,2,NULL,NULL,NULL,NULL] |
+-----------------------------------------+
1 row in set
SELECT array_sort(["hello","hello",NULL,NULL,"OceanBase"]);
+-----------------------------------------------------+
| array_sort(["hello","hello",NULL,NULL,"OceanBase"]) |
+-----------------------------------------------------+
| ["OceanBase","hello","hello",NULL,NULL] |
+-----------------------------------------------------+
1 row in set
array_length
The array_length() function returns the length of an array. For a nested array, it returns the length of the first level (namely, the outermost level) of the array. The syntax is as follows:
array_length(arr)
The parameters are described as follows:
arr: an array. It can be a nested array.
The return value is of the unit32 unsigned integer type.
Here is an example:
SELECT array_length([1,2,3]);
+-----------------------+
| array_length([1,2,3]) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set
SELECT array_length([[1],[2,3]]);
+---------------------------+
| array_length([[1],[2,3]]) |
+---------------------------+
| 2 |
+---------------------------+
1 row in set
array_range
The array_range() function generates an array of evenly spaced values. The syntax is as follows:
array_range(end)
array_range(start, end)
array_range(start, end, step)
The parameters are described as follows:
start: the start value. The default value is0.end: the end value. It must be of an integer type.step: the step length. It must be of an integer type. The default value is1.
The return value is of the BIGINT type. The output result contains the start value but does not contain the end value. If (end - start) / step <= 0, an empty array is returned.
The specific process is as follows:
- The generated array:
array_rangegenerates an array that contains consecutive integers, which range fromstartto, but does not include,end. This means that the value ofstartis the starting value of the generated array, and the value ofendis the ending value of the array. However,enditself is not included in the result. - The step length: If the step length 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]. - The condition for returning an empty array: If
(end - start)/step <= 0, an empty array is returned. This means that ifendis less than or equal tostart, or if the step lengthstepis zero or negative, the generated array will not contain any valid element. In this case, an empty array is returned.
Here is an example:
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 parameters are described as follows:
arr: an array of the numeric type.- Handling of
NULLvalues:- If all array elements are
NULL, the function returnsNULL. - If not all array elements are
NULL,NULLvalues are treated as0during calculation.
- If all array elements are
The return value is described as follows:
- Returns an array type with integer (INT) elements.
- Or returns an array type with floating-point (DOUBLE) elements.
The function is subject to the following constraints:
- Array elements must be numeric types, either integers (such as INT and BIGINT) or floating-point numbers (such as FLOAT and DOUBLE).
- Nested arrays are not supported.
Here is an example:
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_diffrence
The array_diffrence() function calculates the difference between adjacent elements in an array and stores the results in a new array. The syntax is as follows:
array_diffrence(arr)
The parameter is described as follows:
arr: an array of the numeric type.
The return value is of the array type.
The function is subject to the following constraints:
- Array elements must be numeric types, either integers (such as INT and BIGINT) or floating-point numbers (such as FLOAT and DOUBLE).
- Nested arrays are not supported.
The specific process is as follows:
- Calculation method:
- The first element in the return array is fixed to be
0. - The value of the n-th element is
array[n] - array[n-1], namely, the current element minus the previous element.
- The first element in the return array is fixed to be
- Return value:
- A new array where the first element is
0and the subsequent elements are the differences between adjacent elements in the input array.
- A new array where the first element is
Here is an example:
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 parameters are described as follows:
arr: an array of the numeric type.- Handling of
NULLvalue:- If all elements in the array are
NULL, the function returnsNULL. - If not all elements in the array are
NULL,NULLelements 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 function is subject to the following constraint:
- Nested arrays are not supported.
Here is an example:
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 parameters are described as follows:
arr: an array of the numeric type.- Handling of
NULLvalues:- If all elements in the array are
NULL, the function returnsNULL. - If not all elements in the array are
NULL,NULLelements 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 function is subject to the following constraint:
- Nested arrays are not supported.
Here is an example:
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 an array. The syntax is as follows:
array_avg(arr)
The parameters are described as follows:
arr: an array of the numeric type.- Handling of
NULLvalues:- If all elements in the array are
NULL, the function returnsNULL. - If not all elements in the array are
NULL,NULLelements are treated as0for the calculation.
- If all elements in the array are
The return value is of the DOUBLE type.
The function is subject to the following constraints:
- Array elements must be numeric types, either integers (such as INT and BIGINT) or floating-point numbers (such as FLOAT and DOUBLE).
- Nested arrays are not supported.
Here is an example:
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 an array. The syntax is as follows:
array_position(arr, element)
The parameters are described as follows:
arr: an array of the numeric type.element: the target element to be found. It supports basic data types and array types.
The return value is of the INT type. If the element is not found, the return value is 0.
Here is an example:
SELECT array_position([1,2,3], 2);
+--------------------------------+
| array_position([1,2,3], 2) |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set
SELECT array_position(["hello", "hi"], "hi");
+---------------------------------------+
| array_position(["hello", "hi"], "hi") |
+---------------------------------------+
| 2 |
+---------------------------------------+
1 row in set
SELECT array_position(["hello", "hi"], "hel");
+----------------------------------------+
| array_position(["hello", "hi"], "hel") |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set
array_slice
The array_slice() function extracts a specified number of elements from a specified position in an array and returns them in a new array. The syntax is as follows:
array_slice(arr, offset, length)
The parameters are described as follows:
arr: an array of the numeric type.offset: specifies the position of the element to start extracting:- A positive value indicates the
offset-th element from left to right. - A negative value indicates the
-offset-th element from right to left.
- A positive value indicates the
length: specifies the length limit (optional parameter):- A positive value indicates the maximum number of elements that can be extracted.
- A negative value indicates the right boundary of extraction, which is the
-length-th element counted from right to left. - If not provided, all elements starting from
offsetwill be extracted.
The return value is described as follows:
- It is of the array type.
- If
offset>array lengthoroffset=0, an empty array is returned.
Here is an example:
-- offset is positive, indicating the second element when counting from the left
SELECT array_slice([1,2,3,4,5,6,7,8,9],2);
+------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2) |
+------------------------------------+
| [2,3,4,5,6,7,8,9] |
+------------------------------------+
1 row in set
-- offset is positive, indicating the tenth element when counting from the left. In this case, offset is greater than the array length, and 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, and 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 negative, indicating the second element when counting from the right
SELECT array_slice([1,2,3,4,5,6,7,8,9],-2);
+-------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],-2) |
+-------------------------------------+
| [8,9] |
+-------------------------------------+
1 row in set
-- length is positive, indicating two elements to extract
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,2);
+--------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,2) |
+--------------------------------------+
| [2,3] |
+--------------------------------------+
1 row in set
-- length is positive, indicating 10 elements to extract, which exceeds the right boundary
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,10);
+---------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,10) |
+---------------------------------------+
| [2,3,4,5,6,7,8,9] |
+---------------------------------------+
1 row in set
-- length is -2, indicating not to extract the last two elements, namely, 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 to extract the last 10 elements. In this case, the left boundary is exceeded, and an empty array is returned.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,-10);
+----------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,-10) |
+----------------------------------------+
| [] |
+----------------------------------------+
1 row in set
-- offset is -10, indicating to start from the tenth element when counting from the right. In this case, the left boundary is exceeded. Four elements, namely, [1,2,3,4], are extracted from the offset.
SELECT array_slice([1,2,3,4,5,6,7,8,9],-10,4);
+----------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],-10,4) |
+----------------------------------------+
| [1,2,3] |
+----------------------------------------+
1 row in set
-- offset is -6, indicating to start from the sixth element when counting from the right, namely, 4; length is -4, indicating not to extract the last four elements, namely, 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 order of elements in an array. For a nested array, the order of the innermost elements is reversed. The syntax is as follows:
reverse(arr)
The parameter is described as follows:
arr: an array of the numeric type. Nested arrays are supported.
The return value is of the array type.
Here is an example:
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 transforms each element of the input arrays using a lambda function. The transformed elements are combined into a new array, which is returned as the output. The syntax is as follows:
arrayMap((x1,...,xn) -> (expression), arr1,...,arrn)
The parameters are described as follows:
(x1,...,xn) -> (expression): A lambda function, which is an anonymous function that does not require the standarddefstatement for definition but is created using thelambdakeyword. A lambda function can accept any number of parameters but must have only one expression.arr1,...,arrn: input arrays. The input must be of array type, and all arrays must have the same number of elements. The number of arraysnmust match the number of parameters in the lambda function.- The function takes the i-th element of each input array as the input parameter for the lambda function. The return value of the lambda function becomes the i-th element of the new array.
The return value is an array, where the element type matches the return type of the lambda function and the array length matches the number of elements in the input arrays.
The function is subject to 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 generating column expressions.
Here is an example:
SELECT array_map((x,y,z) -> (x is null and y is not null or z is not null), [[1]], [1],['abc']);
+------------------------------------------------------------------------------------------+
| array_map((x,y,z) -> (x is null and y is not null or z is not null), [[1]], [1],['abc']) |
+------------------------------------------------------------------------------------------+
| [1] |
+------------------------------------------------------------------------------------------+
1 row in set
SELECT array_map((x,y) -> (x + y),[1], [2]);
+--------------------------------------+
| array_map((x,y) -> (x + y),[1], [2]) |
+--------------------------------------+
| [3] |
+--------------------------------------+
1 row in set
SELECT array_map((x,y)->((x is null) and (y is null)), [1232], [[['abc']]]);
+----------------------------------------------------------------------+
| array_map((x,y)->((x is null) and (y is null)), [1232], [[['abc']]]) |
+----------------------------------------------------------------------+
| [0] |
+----------------------------------------------------------------------+
1 row in set
SELECT array_map((x,y)->(x+y), array_map(x2->(x2+1),[1,2,3]),array_map(x1->(x1+2),[1,2,3]));
+--------------------------------------------------------------------------------------+
| array_map((x,y)->(x+y), array_map(x2->(x2+1),[1,2,3]),array_map(x1->(x1+2),[1,2,3])) |
+--------------------------------------------------------------------------------------+
| [5,7,9] |
+--------------------------------------------------------------------------------------+
1 row in set
SELECT array_map(x ->(length(x)), ['abc', 'efgaa']);
+----------------------------------------------+
| array_map(x ->(length(x)), ['abc', 'efgaa']) |
+----------------------------------------------+
| [3,5] |
+----------------------------------------------+
1 row in set
SELECT array_map((x, y)->(floor((y - x) / x)), [4, 5, 6], [3,8,5]);
+-------------------------------------------------------------+
| array_map((x, y)->(floor((y - x) / x)), [4, 5, 6], [3,8,5]) |
+-------------------------------------------------------------+
| [-1,0,-1] |
+-------------------------------------------------------------+
1 row in set
array_filter
The array_filter() function filters elements in the arr1 array based on the return value of the lambda function. If the return value of the lambda function is 0 or NULL, the corresponding element in the arr1 array is filtered out, and a new array is returned. The syntax is as follows:
array_filter((x1,...,xn) -> (expression), arr1,...,arrn)
The parameters are described as follows:
(x1,...,xn) -> (expression): A lambda function, which is an anonymous function that does not require the standarddefstatement for definition but is created using thelambdakeyword. A lambda function can accept any number of parameters but must have only one expression.arr1,...,arrn: input arrays. The input must be of array type, and all arrays must have the same number of elements. The number of arraysnmust match the number of parameters in the lambda function. Nested arrays are supported.
The return value is of the array type, which is the arr1 array with filtered elements.
The function is subject to the following constraints:
- The return value of the lambda function must be of the INT type or
NULL.
Here is an example:
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 result of the lambda function. The syntax is as follows:
array_sortby((x1,...,xn) -> (expression), arr1,...,arrn)
The parameters are described as follows:
(x1,...,xn) -> (expression): A lambda function, which is an anonymous function that does not require the standarddefstatement for definition but is created using thelambdakeyword. A lambda function can accept any number of parameters but must have only one expression.arr1,...,arrn: input arrays. The input must be of array type, and all arrays must have the same number of elements. The number of arraysnmust match the number of parameters in the lambda function. Nested arrays are supported.
The return value is of the array type.
The function is subject to the following constraints:
- The return value of the lambda function must be of the basic element type that the array supports, such as an INT or a FLOAT value, but not an array.
The specific process is as follows:
- Generation of the sorting criterion: First,
array_sortbyapplies the lambda function to each element in the input array to generate a new array that contains the results of the lambda function for each element. - Sorting and placement of
NULLvalues: Then, the new array is sorted in ascending order and theNULLvalues are placed at the end. This means that during sorting, theNULLvalues are ignored and all non-NULLvalues are sorted. - Sorting of the original array: Then,
array_sortbysorts the original array based on the sorted order of the new array. This means that the elements in the original array are arranged based on the sorting order of the results of the lambda function. - Return of the sorted array: Finally, the sorted original array is returned.
Here is an example:
SELECT array_sortby(x ->(x), [4,2,1,3]);
+----------------------------------+
| array_sortby(x ->(x), [4,2,1,3]) |
+----------------------------------+
| [1,2,3,4] |
+----------------------------------+
1 row in set
SELECT array_sortby(x ->(x), ['c',NULL,'a',NULL]);
+--------------------------------------------+
| array_sortby(x ->(x), ['c',NULL,'a',NULL]) |
+--------------------------------------------+
| ["a","c",NULL,NULL] |
+--------------------------------------------+
1 row in set
SELECT array_sortby((x,y) ->(y),['a','b','c'], [2,1,3]);
+--------------------------------------------------+
| array_sortby((x,y) ->(y),['a','b','c'], [2,1,3]) |
+--------------------------------------------------+
| ["b","a","c"] |
+--------------------------------------------------+
1 row in set
SELECT array_sortby((x,y) ->(y),[['a'],['b'],['c']], [2,1,3]);
+--------------------------------------------------------+
| array_sortby((x,y) ->(y),[['a'],['b'],['c']], [2,1,3]) |
+--------------------------------------------------------+
| [["b"],["a"],["c"]] |
+--------------------------------------------------------+
1 row in set
array_first
The array_first() function returns the first element in arr1 that makes the lambda function return a value other than 0. The syntax is as follows:
array_first((x1,...,xn) -> (expression), array1,...,arrayn)
The parameters are described as follows:
(x1,...,xn) -> (expression): A lambda function.arr1,...,arrn: input arrays. The input must be of array type, and all arrays must have the same number of elements. The number of arraysnmust match the number of parameters in the lambda function. Nested arrays are supported.
The return value can be of any element type.
Specifically:
- Lambda function: You need to provide a lambda function, which evaluates each element in the array. The return value of the lambda function must be an integer.
- Support for nested arrays: The
array_firstfunction supports nested arrays, meaning you can use an array that contains other arrays. - Return value: The function iterates through the elements in
arr1, finds the first element for which the lambda function returns a non-zero value, and returns it as the result. If no element causes the lambda function to return a non-zero value, the function may returnNULL.
Here is an example:
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
In the preceding example:
- Lambda function:
(x, y) -> (y)is a lambda function that takes 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.
- Calculation of the return value:
- The
array_firstfunction iterates through each element of 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:0(for[1]),1(for[2]), and3(for[3]). - Therefore,
array_firstidentifies the first element in[[1], [2], [3]]for which the lambda function returns a non-zero value. Here,[2]corresponds to theyvalue of1, which is the first non-zero value. The final return value is[2].
- The
array_except
The array_except() function is used to return an array consisting of elements that belong to arr1 but not to 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.
Specifically:
- Nested arrays support: The input arrays can be nested arrays, but the nesting levels of all arrays must be consistent. When the arrays are nested, the operation is performed on the first-level arrays. Even if the elements of two arrays are the same but in a different order, they will be considered different arrays.
- Handling
NULLvalues: If any array element isNULL, the result will returnNULL.
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 is used to compute 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: A list of arrays on which the intersection operation will be performed.
The return value is of array type.
Specifically:
- Nested arrays support: The input arrays can be nested arrays, but the nesting levels of all arrays must be consistent. When the arrays are nested, the operation is performed on the first-level arrays. Even if the elements of two arrays are the same but in a different order, they will be considered different arrays.
- Handling
NULLvalues: There is no special handling forNULLvalues.
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 calculation result will automatically remove duplicates
SELECT array_intersect([1,1,2,2,3], [1,1,2]);
+---------------------------------------+
| array_intersect([1,1,2,2,3], [1,1,2]) |
+---------------------------------------+
| [1,2] |
+---------------------------------------+
1 row in set
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 is used to compute the union of all input arrays. The syntax is as follows:
array_union(arr_list)
The input parameters are described as follows:
arr_list: A list of arrays on which the union operation will be performed.
The return value is of array type.
Specifically:
- Nested arrays support: The input arrays can be nested arrays, but the nesting levels of all arrays must be consistent. When the arrays are nested, the operation is performed on the first-level arrays. Even if the elements of two arrays are the same but in a different order, they will be considered different arrays.
- Handling
NULLvalues: There is no special handling forNULLvalues.
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