Array functions perform basic operations on input array data, such as adding elements, deleting elements, and searching for elements, and return the array data after the operation. OceanBase AI Database currently supports the following array functions: array_append(), array_distinct(), array_remove(), cardinality(), element_at(), string_to_array(), array_agg(), unnest(), split(), and contains().
array_append
The array_append() function is used to append a specified element to the end of a target array. The syntax is as follows:
array_append(arr1, element)
The input parameters are described as follows:
- When
arr1is the base array,elementmust be a basic type supported by arrays: Tinyint, Smallint, Int, Bigint, Float, Double, or VarChar. - When
arr1is a nested array,elementmust be an array with the same nesting level as the sub-arrays inarr1.
The return value is of the array type.
For example:
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 is used to remove duplicates from a target array. The syntax is as follows:
array_distinct(arr1)
The input parameters are described as follows:
- The type of
arr1must be an array.
The return value is of the array type.
If the element types of the arrays are inconsistent (for example, a string array and a numeric array), an error will be returned.
For example:
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 is used to delete a specified element from the target array. The syntax is as follows:
array_remove(arr1, element)
The input parameters are described as follows:
- When
arr1is the base array,elementmust be a basic type supported by arrays: Tinyint, Smallint, Int, Bigint, Float, Double, or VarChar. - When
arr1is a nested array,elementmust be an array with the same nesting depth as the sub-arrays ofarr1.
The return value is of the array type.
An example is as follows:
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 cardinality of the target array. If the target array is a nested array, it returns the sum of the cardinalities of all its non-empty subarrays. The syntax is as follows:
cardinality(arr1)
The input parameters are described as follows:
- The type of
arr1must be an array.
The return value is of the integer type.
Here is an example:
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 index in a target array. The syntax is as follows:
element_at(arr1, index)
The input parameters are described as follows:
- The type of
arr1must be an array. index: the position of the child element to be obtained. The value must be an integer.
The return values are described as follows:
- If
arr1is a nested array, the return type is an array. - If
arr1is a primitive type, the return type is the corresponding primitive type.
For example:
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 is used to convert a string into an array. Specifically, it splits the input string into multiple elements based on the specified delimiter and empty character, and then places the elements into an array in sequence. The delimiter and empty character are case-sensitive. The syntax is as follows:
string_to_array(arr_str, delimiter[, null_str])
The input parameters are described as follows:
- The type of
arr_strmust be a character type, including Char and Varchar. delimiter: The delimiter, which can be a character type, including Char and Varchar.null_str(optional) The null character, which is supported for character types including CHAR and VARCHAR.
The return value is an array of character elements.
An example is as follows:
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 is used to split the string string by the delimiter delimiter and return an array. The syntax is as follows:
split(string, delimiter)
The parameters are described as follows:
- Both
stringanddelimiterare of theVARCHARtype. - The return value type is
ARRAY<varchar>.
An example is as follows:
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 is present in a given container or string, and returns a boolean value. The syntax is as follows:
CONTAINS(array_expr, value)
Here is an example:
SELECT CONTAINS([1, 2, 3, 4, 5], 3), CONTAINS([1, 2, 3, 4, 5], 6), CONTAINS(['a', 'b', 'c'], 'b'), CONTAINS(['a', 'b', 'c'], 'd');
+------------------------------+------------------------------+--------------------------------+--------------------------------+
| CONTAINS([1, 2, 3, 4, 5], 3) | CONTAINS([1, 2, 3, 4, 5], 6) | CONTAINS(['a', 'b', 'c'], 'b') | CONTAINS(['a', 'b', 'c'], 'd') |
+------------------------------+------------------------------+--------------------------------+--------------------------------+
| 1 | 0 | 1 | 0 |
+------------------------------+------------------------------+--------------------------------+--------------------------------+
array_agg
The array_agg() function is used to aggregate multiple rows of data in a specified column into a single array value and return the result. The syntax is as follows:
array_agg([DISTINCT] col [ORDER BY col0 [DESC | ASC] [NULLS FIRST | NULLS LAST] ...])
The input parameters are described as follows:
col: the target column for aggregating data.DISTINCT(optional) specifies whether to perform a deduplication operation on thecolcolumn.ORDER BY(optional) specifies whether to sort the data in thecolcolumn.DESC(optional) specifies a descending order.ASC(optional): specifies the ascending order.NULLS FIRST(optional) specifies to placeNULLvalues at the beginning of the sorted sequence.NULLS LAST(optional): Specifies to placeNULLvalues at the end of the sorted result set.
The return value is of the array type.
The constraints are as follows:
- The
col0specified inORDER BYdoes not support the array type. - The
ORDER BYclause does not support specifying columns for sorting with numbers such as1, 2, .... If numbers are used, theORDER BYclause will be ignored.
An example is as follows:
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 is used to expand elements in an array into multiple rows and return a relational table containing these elements. The syntax is as follows:
unnest(arr_list) [AS] [table_name[(col_name_list)]]
The input parameters are described as follows:
arr_list: one or more array values, separated by commas.table_name(optional): the name of the table to return. The default value isunnest. TheASkeyword can be omitted without affecting the functionality.col_name_list(optional): The column names for each returned column. The default value isunnest. Separate multiple column names with commas. The number of input column names must match the number of array values.
The return values are described as follows:
- Returns a table with the maximum number of elements in the input array as the number of rows and the number of arrays as the number of columns. Columns with fewer elements than others will be padded with
NULLto create empty rows. - For an array with n levels of nesting,
unnestonly unwraps the outermost layer, resulting in an output array with n-1 levels of nesting. - The content of the nth row is the nth sub-element of each array. If an element in
arr_listis a primitive data type, the output type is that element's type. If an element is an array type, the output type is the array type.
The constraints are as follows:
- The
unnest()function can only be used after theFROMclause.
An example is as follows:
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 the beginning of an array and returns a new array. The syntax is as follows:
array_prepend(array, element)
The input parameters are described as follows:
array: Pass an array value.element: The new target element to be added. Its data type must match that of the elements in thearrayparameter.
The return value is of the array type.
An example is as follows:
SELECT array_prepend([1,2,3], 2);
+--------------------------------+
| array_prepend([1,2,3], 2) |
+--------------------------------+
| [2,1,2,3] |
+--------------------------------+
1 row in set
SELECT array_prepend([1,2,3], NULL);
+------------------------------+
| array_prepend([1,2,3], NULL) |
+------------------------------+
| [NULL,1,2,3] |
+------------------------------+
1 row in set
SELECT array_prepend(["1", "2", "a"], "OceanBase");
+---------------------------------------------+
| array_prepend(["1", "2", "a"], "OceanBase") |
+---------------------------------------------+
| ["OceanBase","1","2","a"] |
+---------------------------------------------+
1 row in set
array_concat
The array_concat() function is used to concatenate multiple arrays and return a new array. The syntax is as follows:
array_concat(array_list)
The input parameters are described as follows:
array_list: one or more array values.
The return value is of the array type.
The constraints are as follows:
- Nested array merging is supported, but all arrays must have the same nesting depth. The result returned is the merged result of the elements in the first level (i.e., the outermost level).
An example is as follows:
SELECT array_concat([1,2,3], [4,5,6]);
+--------------------------------+
| array_concat([1,2,3], [4,5,6]) |
+--------------------------------+
| [1,2,3,4,5,6] |
+--------------------------------+
1 row in set
SELECT array_concat([1,2,3], [-4], [5.5,6]);
+--------------------------------------+
| array_concat([1,2,3], [-4], [5.5,6]) |
+--------------------------------------+
| [1,2,3,-4,5.5,6] |
+--------------------------------------+
1 row in set
SELECT array_concat([[1,2,3]],[[11],[22,44]]);
+----------------------------------------+
| array_concat([[1,2,3]],[[11],[22,44]]) |
+----------------------------------------+
| [[1,2,3],[11],[22,44]] |
+----------------------------------------+
1 row in set
array_compact
The array_compact() function removes consecutive duplicate elements from an array and returns a new array. The syntax is as follows:
array_compact(array)
The input parameters are described as follows:
array: Pass an array value.
The return value is of the array type.
An example is as follows:
SELECT array_compact([1,2,2,3,3,2]);
+------------------------------+
| array_compact([1,2,2,3,3,2]) |
+------------------------------+
| [1,2,3,2] |
+------------------------------+
1 row in set
SELECT array_compact(["hello","hello",NULL,NULL,"OceanBase"]);
+--------------------------------------------------------+
| array_compact(["hello","hello",NULL,NULL,"OceanBase"]) |
+--------------------------------------------------------+
| ["hello",NULL,"OceanBase"] |
+--------------------------------------------------------+
1 row in set
SELECT array_compact([[1,2,3,NULL],[4,NULL],[4,NULL]]);
+-------------------------------------------------+
| array_compact([[1,2,3,NULL],[4,NULL],[4,NULL]]) |
+-------------------------------------------------+
| [[1,2,3,NULL],[4,NULL]] |
+-------------------------------------------------+
1 row in set
array_sort
The array_sort() function sorts an array in ascending order, placing NULL values at the end of the array during sorting. The syntax is as follows:
array_sort(arr)
The input parameters are described as follows:
arr: The value of an array to be passed.
The return value is of the array type.
Examples:
SELECT array_sort([2,1,3]);
+---------------------+
| array_sort([2,1,3]) |
+---------------------+
| [1,2,3] |
+---------------------+
1 row in set
SELECT array_sort([NULL,1,2,NULL,2,NULL,NULL]);
+-----------------------------------------+
| array_sort([NULL,1,2,NULL,2,NULL,NULL]) |
+-----------------------------------------+
| [1,2,2,NULL,NULL,NULL,NULL] |
+-----------------------------------------+
1 row in set
SELECT array_sort(["hello","hello",NULL,NULL,"OceanBase"]);
+-----------------------------------------------------+
| array_sort(["hello","hello",NULL,NULL,"OceanBase"]) |
+-----------------------------------------------------+
| ["OceanBase","hello","hello",NULL,NULL] |
+-----------------------------------------------------+
1 row in set
array_length
The array_length() function returns the length of an array. For a nested array, it returns the length of the outermost array. The syntax is as follows:
array_length(arr)
The input parameters are described as follows:
arr: The value of an array to be passed. Nested arrays are supported.
The return value is of the unit32 unsigned integer type.
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 arithmetic progression. The syntax is as follows:
array_range(end)
array_range(start, end)
array_range(start, end, step)
The input parameters are described as follows:
start: The starting value. The default value is0.end: The ending value. Only integers are supported.step: The step size. Only integers are supported. The default value is1.
The return value is an array of BIGINT elements. The output result includes the input start value but excludes the end value. An empty array is returned when (end - start) / step <= 0.
The details are as follows:
- Array generated:
array_rangegenerates an array of consecutive integers that includes thestartelement but excludes theendelement. This means the generated array starts atstartand ends atend, butenditself is not included in the result. - Step: If the step parameter is provided, each element in the generated array will be
stepgreater than the previous one. For example, ifstartis1,endis10, andstepis2, the generated array will be[1, 3, 5, 7, 9]. - Condition for returning an empty array: The function returns an empty array when
(end - start)/step <= 0. This means that ifendis less than or equal tostart, or if the step sizestepis zero or negative, the generated array will have no valid elements, so an empty array is returned.
An example is as follows:
SELECT array_range(5);
+----------------+
| array_range(5) |
+----------------+
| [0,1,2,3,4] |
+----------------+
1 row in set
SELECT array_range(-1,4);
+-------------------+
| array_range(-1,4) |
+-------------------+
| [-1,0,1,2,3] |
+-------------------+
1 row in set
SELECT array_range(-1,4,2);
+---------------------+
| array_range(-1,4,2) |
+---------------------+
| [-1,1,3] |
+---------------------+
1 row in set
array_sum
The array_sum() function calculates the sum of all elements in an array. The syntax is as follows:
array_sum(arr)
The input parameters are described as follows:
arr: an array value of numeric elements.- Handling of
NULLvalues:- If all array elements are
NULL, returnNULL. - If not all array elements are
NULL, theNULLvalues are treated as0for calculation.
- If all array elements are
The return values are described as follows:
- Returns an array type for integer (INT) elements.
- or return an array type of floating-point (DOUBLE) elements.
The following functional constraints apply:
- Array elements can only be of integer types (including INT and BIGINT) or floating-point types (including FLOAT and DOUBLE).
- Nested arrays are not supported.
For 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_difference
The array_difference() function calculates the difference between every two adjacent elements in an array and stores the results in a new array. The syntax is as follows:
array_difference(arr)
The input parameters are described as follows:
- The type of
arrmust be an array.
The return value is of the array type.
The following functional constraints apply:
- Array elements can only be of integer (including INT and BIGINT) or floating-point (including FLOAT and DOUBLE) data types.
- Nested arrays are not supported.
The details are as follows:
- Calculation method:
- The first element in the returned array is always
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 one.
- The first element in the returned array is always
- Return value:
- Returns a new array where the first element is
0, and subsequent elements are the differences between adjacent elements.
- Returns 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 input parameters are described as follows:
- The type of
arrmust be an array. - Handling of
NULLvalues:- If all array elements are
NULL, returnNULL. - If not all array elements are
NULL, skip theNULLelements.
- If all array elements are
The return value is the data type of the input array element.
The functional constraints are as follows:
- Nested arrays are not supported.
For 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 input parameters are described as follows:
- The type of
arrmust be an array. - Handling of
NULLvalues:- If all array elements are
NULL, returnNULL. - If not all array elements are
NULL, skip theNULLelements.
- If all array elements are
The return value is the data type of the input array element.
The following functional constraints apply:
- Nested arrays are not supported.
An example is as follows:
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:
- The type of
arrmust be an array. - Handling of
NULLvalues:- If all array elements are
NULL, returnNULL. - If not all array elements are
NULL, theNULLvalues are treated as0for calculation.
- If all array elements are
The return value is an array of floating-point (DOUBLE) elements.
The following functional constraints apply:
- Array elements can only be of integer types (including INT and BIGINT) or floating-point types (including 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 is used to find 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:
- The type of
arrmust be an array. element: The element to be found. It can be a basic type or an array supported by arrays.
The return value is an array of INT elements. If the element does not exist, 0 is returned.
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 is used to extract a portion of elements from an array starting at a specified position and return the extracted elements as a new array. The syntax is as follows:
array_slice(arr, offset, length)
The input parameters are described as follows:
- The type of
arrmust be an array. offset: The position of the first element to be extracted.- If a positive number is specified, the extraction starts from the
offset-th element from the left. - When a negative number is input, it indicates the
-offsetth element from the right.
- If a positive number is specified, the extraction starts from the
length: the maximum length of the value. This parameter is optional. Valid values:- A positive input indicates the maximum number of elements that can be extracted.
- A negative input indicates the right boundary of the extraction, which is the
-length-th value counted from the right. - If this parameter is not specified, all elements starting from
offsetare returned.
The return values are described as follows:
- The return value is of an array type.
- Returns an empty array when
offset > length of the arrayoroffset = 0.
An example is as follows:
-- If the offset is positive, it indicates the second character from the left, meaning extraction starts at index 2.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2);
+------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2) |
+------------------------------------+
| [2,3,4,5,6,7,8,9] |
+------------------------------------+
1 row in set
-- When the offset is positive, it indicates the 10th position from the left. In this case, the offset exceeds 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
-- When offset is 0, an empty array is returned.
SELECT array_slice([1,2,3,4,5,6,7,8,9],0);
+------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],0) |
+------------------------------------+
| [] |
+------------------------------------+
1 row in set
-- A negative offset indicates the second digit from the right, meaning extraction starts from 8.
SELECT array_slice([1,2,3,4,5,6,7,8,9],-2);
+-------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],-2) |
+-------------------------------------+
| [8,9] |
+-------------------------------------+
1 row in set
-- A positive length indicates extracting two 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
-- If length is a positive number, it indicates that 10 elements are taken, 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
-- When length is -2, it means to skip the last two elements from the right, i.e., to skip 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
-- A length of -10 indicates that the last 10 elements from the right are not to be taken, which exceeds the left boundary and returns an empty array.
SELECT array_slice([1,2,3,4,5,6,7,8,9],2,-10);
+----------------------------------------+
| array_slice([1,2,3,4,5,6,7,8,9],2,-10) |
+----------------------------------------+
| [] |
+----------------------------------------+
1 row in set
-- When the offset is -10, it indicates that extraction starts from the 10th position from the right. In this case, one bit beyond the left boundary is taken, and then length (4) bits are extracted 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
-- The `offset` parameter is set to `-6`, which means the extraction starts from the 6th position from the right, i.e., the value is `4`. The `length` parameter is set to `-4`, which means the next four values from the right are skipped, i.e., the values `6`, `7`, `8`, and `9` are skipped.
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 is used to reverse the order of elements in an array. For nested arrays, it reverses the order of the innermost elements. The syntax is as follows:
reverse(arr)
The input parameters are described as follows:
- The type of
arrmust be an array. Nested arrays are supported.
The return value is of the array type.
For 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 applies a Lambda function to each element of the input arrays, combining all the transformed elements into a single new array as the output. The syntax is as follows:
arrayMap((x1,...,xn) -> (expression), arr1,...,arrn)
The input parameters are described as follows:
(x1,...,xn) -> (expression)A lambda function. A lambda function is an anonymous function that does not require the usualdefstatement for definition; instead, it is created using thelambdakeyword. A lambda function can accept any number of parameters but can have only one expression.arr1,...,arrn: The input arrays. The type must be an array, and all elements must have the same number of elements;nmust match the number of parameters in the Lambda function.- Sequentially extract the i-th element from each input array as input parameters for the Lambda function, and use the return values as the i-th elements of the new array.
The return value is an array whose elements are of the return type of the lambda expressions, and the length of the array is the number of elements in the input array.
The functional constraints are as follows:
- The
expressionof a Lambda function does not support subqueries. - Aggregation functions are not supported in the
expressionof a Lambda function. - The
expressionof a Lambda function cannot be used as an expression for a generated column.
For 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
array_filter() filters the elements of the arr1 array based on the return values of the Lambda function. If the Lambda function returns 0 or NULL, the corresponding arr1 element is removed, 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)Lambda function. A lambda function is an anonymous function that does not require the usualdefstatement for definition; instead, it is created using thelambdakeyword. A lambda function can accept any number of parameters but can have only one expression.arr1,...,arrn: The input arrays. The type must be an array, and all elements must have the same length;nmust 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 functional constraints apply:
- The return value of the Lambda function must be an integer or
NULL.
For 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 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)Lambda function. A Lambda function is an anonymous function that does not require the usualdefstatement for definition; instead, it is created using thelambdakeyword. A Lambda function can accept any number of parameters but can have only one expression.arr1,...,arrnInput arrays. The type passed 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 the array type.
The functional constraints are as follows:
- Only basic element types that are arrays support being returned by a Lambda function; array types are not supported.
The specific workflow is as follows:
- Generate the sort key: First,
array_sortbycalculates the result of the provided Lambda function for each element in the input array, generating a new array that contains the results of the Lambda function application on each element. - Sort in ascending order and handle
NULLvalues: Next, this newly generated array is sorted in ascending order, withNULLvalues placed at the end. This means that during sorting, all non-NULLvalues are prioritized, whileNULLvalues are placed at the end of the sorted result. - Re-sort the original array according to the sort order: Finally,
array_sortbysorts the original array according to the sort order of the newly generated array. This means the elements in the original array are arranged in the order of the results calculated by the Lambda function. - Return the sorted array: Ultimately, the function returns the sorted original array.
An example is as follows:
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)Provides a Lambda function.arr1,...,arrnInput arrays. The type passed 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.
The specific description is as follows:
- Lambda function: You need to provide a Lambda function that evaluates each element in the array. The return value of the Lambda function must be an integer.
- Arrays support nesting: The
array_firstfunction supports nested arrays, which means you can pass in 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 value other than0, and returns that element as the result. If no element causes the Lambda function to return a non-zero value,NULLmay be returned.
An example is as follows:
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 example is described as follows:
- Lambda function:
(x,y) -> (y)is a Lambda function that takes two parametersxandyand returns the value ofy. Thexparameter is not actually used in this specific call. - Input array:
[[1],[2],[3]]is a nested array containing three sub-arrays.[0,1,3]is a one-dimensional array containing three integers.
- Calculation of return values:
- The
array_firstfunction iterates through each element in the input array[[1],[2],[3]], and calls the Lambda function on each element, evaluating it with the correspondingyvalue from[0,1,3]. - In this example, the Lambda function returns
0,1, and3, corresponding to0(for[1]),1(for[2]), and3(for[3]) respectively. - Therefore,
array_firstfinds the first element in[[1],[2],[3]]for which the Lambda function returns a value other than0. Here, theyvalue corresponding to[2]is1, which is the first non-zero value. The final return value is[2].
- The
array_except
The array_except() function returns an array 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 for calculation.arr2: The right array for calculation.
The return value is of the array type.
The details are as follows:
- Arrays support nesting: The input arrays can be nested, but all arrays must have the same nesting depth. When an array is nested, the elements used in the operation are from the first-level array. Even if two arrays have the same elements but different orders, they are considered different arrays.
- Handling of
NULLvalues: If any element in the array isNULL, the result isNULL.
Here is an example:
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: A list of arrays for which you want to calculate the intersection.
The return value is of the array type.
Specific details are as follows:
- Arrays support nesting: Input arrays can be nested arrays, but all arrays must have the same nesting depth. When an array is a nested array, the elements used in the operation are from the first-level array. Even if two arrays have the same elements but in a different order, they are considered different arrays.
- Handling of
NULLvalues: No special handling is required.
Examples:
SELECT array_intersect([1,2,3], [1,2]);
+---------------------------------+
| array_intersect([1,2,3], [1,2]) |
+---------------------------------+
| [1,2] |
+---------------------------------+
1 row in set
-- The calculation results will be automatically deduplicated.
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: A list of arrays for which you want to calculate the union.
The return value is of the array type.
Specific details are as follows:
- Arrays support nesting: Input arrays can be nested arrays, but all arrays must have the same nesting depth. When an array is a nested array, the elements used in the operation are from the first-level array. Even if two arrays have the same elements but in a different order, they are considered different arrays.
- Handling of
NULLvalues: No special handling is required.
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