Function Name | Statement | Description |
[x] | Used to obtain an element from an array, equivalent to the element_at function. | |
array_agg(x) | Returns all values in x as an array. | |
array_distinct(x) | Removes duplicate values from an array, returning only unique elements. | |
array_except(x,y) | Returns the difference between arrays x and y. | |
array_intersect(x, y) | Returns the intersection between arrays x and y. | |
array_join(x, delimiter) | Concatenates the elements in an array into a string using the specified delimiter. Null elements in the array will be ignored. Note: The return result of the array_join function is limited to a maximum of 1 KB. Any data exceeding this limit will be truncated. | |
| array_join(x, delimiter, null_replacement) | Concatenates the string arrays into a string using the specified delimiter. Null values will be replaced by null_replacement. Note: The return result of the array_join function is limited to a maximum of 1 KB. Any data exceeding this limit will be truncated. |
array_max(x) | Obtains the maximum value in an array. | |
array_min(x) | Obtains the minimum value in an array. | |
array_position(x, element) | Obtains the subscript of a specified element, starting from 1. If the element does not exist, 0 is returned. | |
array_remove(x, element) | Deletes a specified element from an array. | |
array_sort(x) | Sorts the elements in an array in ascending order. Null elements will be placed at the end. | |
array_union(x, y) | Calculates the union of two arrays. | |
cardinality(x) | Calculates the number of elements in an array. | |
concat(x, y...) | Concatenates multiple arrays into a single array. | |
contains(x, element) | Determines whether an array contains a specified element. If yes, true is returned. | |
element_at(x, y) | Returns the y-th element in an array. | |
filter(x, lambda_expression) | Filters elements from an array using a lambda expression, returning only the elements that satisfy the condition. | |
flatten(x) | Converts a two-dimensional array into a one-dimensional array. | |
reduce(x, lambda_expression) | Calculates the sum of the elements in an array according to the rules defined in the lambda expression, returning the final calculated value. | |
reverse(x) | Reverses the order of elements in an array. | |
sequence(x, y) | Returns an array of sequentially increasing integers starting from a specified value and within a defined range. The step defaults to 1. | |
| sequence(x, y, step) | Returns an array of sequentially increasing integers starting from a specified value and within a defined range. The step can be customized. |
shuffle(x) | Shuffles the elements in an array. | |
slice(x, start, length) | Returns a subset of an array. | |
transform(x, lambda_expression) | Applies a lambda expression to each element in an array. | |
zip(x, y) | Merges multiple arrays into a two-dimensional array. Elements with the same subscript in each original array form a new array. | |
zip_with(x, y, lambda_expression) | Merges two arrays into a single array by applying a lambda expression. | |
[x]
Parameter | Description |
x | Array subscript, starting from 1. The parameter value is of the bigint type. |
array:[12,23,26,48,26]
* | SELECT cast(json_parse(array) as array(bigint)) [2]
23
array_agg(x)
Parameter | Description |
x | The parameter value can be of any data type. |
* | SELECT array_agg(status) AS array
[200,200,200,404,200,200]
array_distinct function is used to delete duplicate elements from an array.array_distinct(x)
Parameter | Description |
x | The parameter value is of the array type. |
array field.array:[12,23,26,48,26]
* | SELECT array_distinct(cast(json_parse(array) as array(bigint)))
[12,23,26,48]
array_except function is used to calculate the difference between two arrays.array_except(x, y)
Parameter | Description |
x | The parameter value is of the array type. |
y | The parameter value is of the array type. |
* | SELECT array_except(array[1,2,3,4,5],array[1,3,5,7])
[2,4]
array_intersect function is used to calculate the intersection between two arrays.array_intersect(x, y)
Parameter | Description |
x | The parameter value is of the array type. |
y | The parameter value is of the array type. |
* | SELECT array_intersect(array[1,2,3,4,5],array[1,3,5,7])
[1,3,5]
array_join function is used to concatenate the elements in an array into a string using the specified delimiter.array_join(x, delimiter)
null_replacement.array_join(x, delimiter, null_replacement)
Parameter | Description |
x | The parameter value is of the array type. |
delimiter | Connector, which can be a string. |
null_replacement | String used to replace a null element. |
region.* | SELECT array_join(array[null,'China','sh'],'/','region')
region/China/sh
array_max function is used to get the maximum value of an array.array_max(x)
Parameter | Description |
x | The parameter value is of the array type. |
array:[12,23,26,48,26]
* | SELECT array_max(try_cast(json_parse(array) as array(bigint))) AS max_number
48
array_min function is used to get the minimum value of an array.array_min(x)
Parameter | Description |
x | The parameter value is of the array type. |
array:[12,23,26,48,26]
* | SELECT array_min(try_cast(json_parse(array) as array(bigint))) AS min_number
12
array_position function is used to get the subscript (starting from 1) of a specified element. If the specified element does not exist, return 0.array_position(x, element)
Parameter | Description |
x | The parameter value is of the array type. |
element | Element in an array. |
* | SELECT array_position(array[23,46,35],46)
2
array_remove function is used to delete a specified element from an array.array_remove(x, element)
Parameter | Description |
x | The parameter value is of the array type. |
element | Element in an array. |
* | SELECT array_remove(array[23,46,35],23)
[46,35]
array_sort function is used to sort elements in an array in ascending order.array_sort(x)
Parameter | Description |
x | The parameter value is of the array type. |
* | SELECT array_sort(array['b','d',null,'c','a'])
["a","b","c","d",null]
array_union function is used to calculate the union of two arrays.array_union(x, y)
Parameter | Description |
x | The parameter value is of the array type. |
y | The parameter value is of the array type. |
* | SELECT array_union(array[1,2,3,4,5],array[1,3,5,7])
[1,2,3,4,5,7]
cardinality function is used to calculate the number of elements in an array.cardinality(x)
Parameter | Description |
x | The parameter value is of the array type. |
number field value.array:[12,23,26,48,26]
* | SELECT cardinality(cast(json_parse(array) as array(bigint)))
5
concat function is used to concatenate multiple arrays into one.concat(x, y...)
Parameter | Description |
x | The parameter value is of the array type. |
y | The parameter value is of the array type. |
* | SELECT concat(array['red','blue'],array['yellow','green'])
["red","blue","yellow","green"]
contains function is used to determine whether an array contains a specified element and return true if the array contains the element.contains(x, element)
Parameter | Description |
x | The parameter value is of the array type. |
element | Element in an array. |
array field value contains 23.array:[12,23,26,48,26]
* | SELECT contains(cast(json_parse(array) as array(varchar)),'23')
TRUE
element_at function is used to return the yth element in an array.element_at(x, y)
Parameter | Description |
x | The parameter value is of the array type. |
element | Array subscript, starting from 1. The parameter value is of the bigint type. |
number field value.array:[12,23,26,48,26]
* | SELECT element_at(cast(json_parse(number) AS array(varchar)), 2)
23
filter function is used to filter elements in an array and return only the elements that comply with a specified Lambda expressionfilter(x, lambda_expression)
Parameter | Description |
x | The parameter value is of the array type. |
lambda_expression |
x -> x > 0 is the Lambda expression.* | SELECT filter(array[5,-6,null,7],x -> x > 0)
[5,7]
flatten function is used to convert a two-dimensional array to a one-dimensional array.flatten(x)
Parameter | Description |
x | The parameter value is of the array type. |
* | SELECT flatten(array[array[1,2,3,4],array[4,3,2,1]])
[1,2,3,4,4,3,2,1]
reduce function is used to add the elements in an array as defined by the Lambda expression and return the result.reduce(x, lambda_expression)
Parameter | Description |
x | The parameter value is of the array type. |
lambda_expression |
* | SELECT reduce(array[5,20,50],0,(s, x) -> s + x, s -> s)
75
reverse function is used to reverse the elements in an array.reverse(x)
Parameter | Description |
x | The parameter value is of the array type. |
* | SELECT reverse(array[1,2,3,4,5])
[5,4,3,2,1]
sequence function is used to return an array of consecutive and increasing values within the specified starting value range.1.sequence(x, y)
sequence(x, y, step)
Parameter | Description |
x | The parameter value is of the bigint or timestamp type (UNIX timestamp or date and time expression). |
y | The parameter value is of the bigint or timestamp type (UNIX timestamp or date and time expression). |
step | The numerical interval between values. If the parameter value is a date and time expression, the format of the step is as follows: interval 'n' year to month: Indicates an interval of n years. interval 'n' day to second: Indicates an interval of n days. |
* | SELECT sequence(0,10,2)
[0,2,4,6,8,10]
* | SELECT sequence(from_unixtime(1508737026),from_unixtime(1628734085),interval '1' year to month )
["2017-10-23 05:37:06.0","2018-10-23 05:37:06.0","2019-10-23 05:37:06.0","2020-10-23 05:37:06.0"]
* | SELECT sequence(1628733298,1628734085,60)
[1628733298,1628733358,1628733418,1628733478,1628733538,1628733598,1628733658,1628733718,1628733778,1628733838,1628733898,1628733958,1628734018,1628734078]
shuffle(x)
Parameter | Description |
x | The parameter value is of the array type. |
* | SELECT shuffle(array[1,2,3,4,5])
[5,2,4,1,3]
slice(x, start, length)
Parameter | Description |
x | The parameter value is of the array type. |
start | The starting index position. A negative value counts backward from the end of the array. A positive value counts forward from the start of the array. |
length | Number of elements in a subset. |
* | SELECT slice(array[1,2,4,5,6,7,7],3,2)
[4,5]
transform function is used to apply a Lambda expression to each element of an array.transform(x, lambda_expression)
Parameter | Description |
x | The parameter value is of the array type. |
lambda_expression |
* | SELECT transform(array[5,6],x -> x + 1)
[6,7]
zip function is used to combine multiple arrays into a two-dimensional array, and elements with the same subscript in each original array form a new array.zip(x, y)
Parameter | Description |
x | The parameter value is of the array type. |
y | The parameter value is of the array type. |
* | SELECT zip(array[1,2], array[3,4])
["{1, 3}","{2, 4}"]
zip_with function is used to merge two arrays into one as defined by a Lambda expression.zip_with(x, y, lambda_expression)
Parameter | Description |
x | The parameter value is of the array type. |
y | The parameter value is of the array type. |
lambda_expression |
(x, y) -> x + y to add the elements in arrays [1,2] and [3,4], respectively, and return the sum results as an array.* | SELECT zip_with(array[1,2], array[3,4],(x,y) -> x + y)
[4,6]
피드백