Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ SQL Functions and Operators/ Array Functions and Operators

Updated on 2024-10-25 GMT+08:00

View PDF

Array Functions and Operators

Subscript Operator: []

Description: Subscript operators are used to access elements in an array and create indexes starting from 1.

select myarr[5] from (values array [1,4,6,78,8,9],array[2,4,6,8,10,12]) as t(myarr);
 _col0 
-------
     8 
    10

Concatenation Operator: ||

|| Operators are used to concatenate arrays or values of the same type.

SELECT ARRAY[1] || ARRAY[2];
 _col0  
--------
 [1, 2] 
(1 row)

SELECT ARRAY[1] || 2;
 _col0  
--------
 [1, 2] 
(1 row)

SELECT 2 || ARRAY[1];
 _col0  
--------
 [2, 1] 
(1 row)

Array Function

Subscript operator: []

The subscript operator [] is used to obtain the value of the corresponding position in the array.

SELECT ARRAY[5,3,41,6][1] AS first_element; -- 5

Concatenation Operator: ||

The || operator is used to concatenate an array with an array or an element of the same type:

SELECT ARRAY[1]||ARRAY[2];-- [1, 2]
SELECT ARRAY[1]||2; -- [1, 2]
SELECT 2||ARRAY[1];-- [2, 1]

array_contains(x, element) → boolean
Description: Returns true if the array x contains elements.
```
select array_contains (array[1,2,3,34,4],4); -- true
```
all_match(array(T), function(T, boolean)) → boolean
Description: Returns whether all elements of an array satisfy the given assertion function. If all elements satisfy the assertion function or the array is empty, true is returned. If one or more elements do not satisfy the assertion function, false is returned. If the assertion function is not satisfied by one or more elements, it returns null, and the return value of the all_match function is also null.
```
select all_match(a, x-> true) from (values array[]) t(a); -- true
select all_match(a, x-> x>2) from (values array[4,5,7]) t(a); -- true
select all_match(a, x-> x>2) from (values array[1,5,7]) t(a); -- false
select all_match(a, x-> x>1) from (values array[NULL, NULL ,NULL]) t(a); --NULL
```
any_match(array(T), function(T, boolean)) → boolean
Description: Returns whether an array has elements that satisfy the assertion function. If one or more elements satisfy the assertion function, true is returned. If all elements cannot satisfy the assertion function or the array is empty, false is returned. If the assertion function is not satisfied by one or more elements, it returns null, and the return value of the all_match function is also null.
```
select any_match(a, x-> true) from (values array[]) t(a); -- false
select any_match(a, x-> x>2) from (values array[0,1,2]) t(a); -- false
select any_match(a, x-> x>2) from (values array[1,5,7]) t(a); -- true
select any_match(a, x-> x>1) from (values array[NULL, NULL ,NULL]) t(a); -- NULL
```

array_distinct(x) → array

Description: Outputs the deduplicated array x.

select  array_distinct(array [1,1,1,1,1,1,3,3,3,33,4,5,6,6,6,6]);--  [1, 3, 33, 4, 5, 6]

array_intersect(x, y) → array

Description: Returns the intersection of two arrays after deduplication.

select array_intersect(array [1,3,5,7,9],array [1,2,3,4,5]);
   _col0   
-----------
 [1, 3, 5] 
(1 row)

array_union(x, y) → array

Description: Returns the union of two arrays.

select array_union(array [1,3,5,7,9],array [1,2,3,4,5]);
         _col0         
-----------------------
 [1, 3, 5, 7, 9, 2, 4] 
(1 row)

array_except(x, y) → array
Description: Returns an array of deduplicated elements that are in x but not in y.
```
select array_except(array [1,3,5,7,9],array [1,2,3,4,5]);
 _col0  
--------
 [7, 9] 
(1 row)
```
array_join(x, delimiter, null_replacement) → varchar
Description: Concatenates elements of a given array x with delimiters and replaces the null value in x with optional characters.
```
select array_join(array[1,2,3,null,5,6],'|','0');--  1|2|3|0|5|6 
```
array_max(x) → x
Description: Returns the maximum value of array x.
```
select array_max(array[2,54,67,132,45]);  -- 132
```
array_min(x) → x
Description: Returns the minimum value of array x.
```
select array_min(array[2,54,67,132,45]);  -- 2
```
array_position(x,element) → bigint
Description: Returns the first occurrence position of an element in the array x. If the element is not found, 0 is returned.
```
select array_position(array[2,3,4,5,1,2,3],3); -- 2
```
array_remove(x, element) → array
Description: Removes elements whose values are elements from array x and returns the elements.
```
select array_remove(array[2,3,4,5,1,2,3],3); --  [2, 4, 5, 1, 2] 
```
array_sort(x) → array
Sorts and returns the array x. The elements of x must be sortable. The empty element is placed at the end of the returned array.
```
select array_sort(array[2,3,4,5,1,2,3]); -- [1, 2, 2, 3, 3, 4, 5]
```

array_sort(array(T), function(T, T, int))

Description: Sorts and returns the array based on the given comparator function. The comparator will use two parameters that can be null, representing the two elements of the array that can be null. When the first element that can be empty is less than, equal to, or greater than the second element that can be empty, -1, 0, or 1 is returned. If the comparator function returns other values, including null, the query fails and an error is thrown.

SELECT array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1)));
 _col0  
--------
[5, 3, 2, 2, 1]
(1 row)

SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1)));
 _col0  
--------
[dc, bc, ab]
(1 row)

-- The null value is placed in the front, and other values are sorted in descending order.
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2],
                  (x, y) -> CASE WHEN x IS NULL THEN -1
                                 WHEN y IS NULL THEN 1
                                 WHEN x < y THEN 1
                                 WHEN x = y THEN 0
                                 ELSE -1 END);
 _col0  
--------
[null, null, 5, 3, 2, 2, 1]
(1 row)

-- The null value is placed in the back, and other values are sorted in descending order.
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2],  
                 (x, y) -> CASE WHEN x IS NULL THEN 1
                                 WHEN y IS NULL THEN -1
                                 WHEN x < y THEN 1
                                 WHEN x = y THEN 0
                                 ELSE -1 END);  
 _col0  
--------
[5, 3, 2, 2, 1, null, null]
(1 row)

-- Sorting by character string length:
SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], 
                  (x, y) -> IF(length(x) < length(y), -1,
                               IF(length(x) = length(y), 0, 1)));
 _col0  
--------
[a, abc, abcd]
(1 row)

-- Sorting by array length:
SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], 
                  (x, y) -> IF(cardinality(x) < cardinality(y),
                               -1,
                               IF(cardinality(x) = cardinality(y), 0, 1))); 
 _col0  
--------
[[1, 2], [2, 3, 1], [4, 2, 1, 4]]
(1 row)

arrays_overlap(x, y)
Description: Returns true if two arrays have common non-null elements. Otherwise, false is returned.
```
select arrays_overlap(array[1,2,3],array[3,4,5]);-- true
select arrays_overlap(array[1,2,3],array[4,5,6]);-- false
```
cardinality(x)
Description: Returns the capacity of array x.
```
select cardinality(array[1,2,3,4,5,6]); --6
```
concat(array1, array2, ..., arrayN)
Description: Provides the same function as the SQL standard connection operator (||).
combinations(array(T), n) -> array(array(T))
Description: Returns n element subgroups of the input array. If the input array has no duplicate items, the combination returns a subset of n elements:
```
SELECT combinations(ARRAY['foo', 'bar', 'baz'], 2); -- [[foo, bar], [foo, baz], [bar, baz]]

SELECT combinations(ARRAY[1, 2, 3], 2); -- [[1, 2], [1, 3], [2, 3]]

SELECT combinations(ARRAY[1, 2, 2], 2); -- [[1, 2], [1, 2], [2, 2]]
```
The subgroups and the elements in the subgroups, though not specified, are ordered. The value of n cannot be greater than 5, and the maximum number of generated subgroups cannot exceed 100000.
contains(x, element)
Description: Returns true if the array x contains elements.
```
select contains(array[1,2,3,34,4],4); -- true
```
element_at(array(E), index)
Description: Returns the elements of an array at the given index. If the value of index is greater than 0, this function provides the same function as the SQL standard subscript operator ([]). However, when this function accesses an index whose length is greater than the array length, null is returned, and the subscript operator fails in this case. If the index is less than 0, element_at accesses elements from the last to the first.
```
select element_at(array['a','b','c','d','e'],3); -- c
```

filter(array(T), function(T, boolean)) -> array(T)

Description: Deletes an array consisting of elements whose operation result is true.

SELECT filter(ARRAY [], x -> true); -- []

SELECT filter(ARRAY [5, -6, NULL, 7], x -> x > 0); -- [5, 7]

SELECT filter(ARRAY [5, NULL, 7, NULL], x -> x IS NOT NULL); -- [5, 7]

flatten(x)
Description: Expands array(array(T)) to array(T) in series.

ngrams(array(T), n) -> array(array(T))

Description: Returns the n-gram (subsequence of n adjacent elements) of an array. The sequence of n-grams in the result is not specified.

SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 2); -- [[foo, bar], [bar, baz], [baz, foo]]

SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 3); -- [[foo, bar, baz], [bar, baz, foo]]

SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 4); -- [[foo, bar, baz, foo]]

SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 5); -- [[foo, bar, baz, foo]]

SELECT ngrams(ARRAY[1, 2, 3, 4], 2); -- [[1, 2], [2, 3], [3, 4]]

none_match(array(T), function(T, boolean))
Description: Returns whether an array has no elements matching the given predicate. If no element matches the predicate, true is returned (a special case is when the array is empty). The value is false if one or more elements match. The value is null if the predicate function returns null for one or more elements and false for all other elements.

reduce(array(T), initialState S, inputFunction(S, T, S), outputFunction(S, R))

Returns a single value reduced from an array. inputFunction is called for each element in the array in sequence. In addition to getting the element, inputFunction also gets the current state, initially initialState, and then returns the new state. outputFunction will be called to convert the final state to a result value. It may be a constant function (i-> i).

SELECT reduce(ARRAY [], 0, (s, x) -> s + x, s -> s); -- 0
SELECT reduce(ARRAY [5, 20, 50], 0, (s, x) -> s + x, s -> s); -- 75
SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + x, s -> s); -- NULL
SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + COALESCE(x, 0), s -> s); -- 75
SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> IF(x IS NULL, s, s + x), s -> s); -- 75
SELECT reduce(ARRAY [2147483647, 1], CAST (0 AS BIGINT), (s, x) -> s + x, s -> s); -- 2147483648
SELECT reduce(ARRAY [5, 6, 10, 20],              CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)),

              (s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS ROW(sum DOUBLE, count INTEGER)),
              s -> IF(s.count = 0, NULL, s.sum / s.count)); -- 10.25

repeat(element, count)
Description: Number of times that the element is repeatedly output. The value is filled in the array.
```
select repeat(4,5);-- [4, 4, 4, 4, 4] 
```
reverse(x)
Description: Populates the returned array with array elements in reverse order.
```
select reverse(array[1,2,3,4,5]); --[5, 4, 3, 2, 1]
```
sequence(start, stop)
Description: Outputs an array from start to stop. If the value of start is not greater than that of stop, the value is incremented by 1 each time. Otherwise, the value is decremented by 1 each time.

The data type of start and stop can also be date or timestamp, which increases or decreases by one day.
```
select sequence(5,1);
      _col0      
-----------------
 [5, 4, 3, 2, 1] 
(1 row)

select sequence(5,10);
        _col0        
---------------------
 [5, 6, 7, 8, 9, 10] 
(1 row)
```
sequence(start, stop, step)→ array
Description: Outputs from start to stop with a step length.
```
select sequence(1,30,5);--  [1, 6, 11, 16, 21, 26] 
```

shuffle(x)→ array

Description: Gets a new array based on the given array randomization.

select shuffle(array[1,2,3,4,5]);-- [1, 5, 4, 2, 3] 
select shuffle(array[1,2,3,4,5]);--  [2, 1, 3, 5, 4]

size(x) → bigint
Description: Returns the capacity of array x.
```
select size(array[1,2,3,4,5,6]); --6
```
slice(x, start, length) → array
Description: Subset array x starts from the beginning of the index (if the start is negative, the array starts from the end) and its length is length.
```
select slice(array[1,2,3,4,5,6],2,3);-- [2, 3, 4] 
```
sort_array(x)
For details, see array_sort(x).

trim_array(x, n) → array

Description: Deletes the last n elements in an array.

SELECT trim_array(ARRAY[1, 2, 3, 4], 1); -- [1, 2, 3] 
SELECT trim_array(ARRAY[1, 2, 3, 4], 2); -- [1, 2]

transform(array(T), function(T, U)) -> array(U)

Description: Returns an array that is the result of applying the function to each element of the array.

SELECT transform(ARRAY [], x -> x + 1); -- []
SELECT transform(ARRAY [5, 6], x -> x + 1); -- [6, 7]
SELECT transform(ARRAY [5, NULL, 6], x -> COALESCE(x, 0) + 1); -- [6, 1, 7]
SELECT transform(ARRAY ['x', 'abc', 'z'], x -> x || '0'); -- [x0, abc0, z0]
SELECT transform(ARRAY [ARRAY [1, NULL, 2], ARRAY[3, NULL]], a -> filter(a, x -> x IS NOT NULL)); -- [[1, 2], [3]]

zip(array1, array2[, ...]) -> array(row)
Description: Merges a given array into a single row array by element. The Mth element of the Nth independent variable is the Nth field of the Mth output element. If the parameter length is uneven, the missing value is filled with null.
```
SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); --  [{1, 1b}, {2, NULL}, {NULL, 3b}]
```

zip_with(array(T), array(U), function(T, U, R)) -> array(R)

Description: Combines two given arrays one by one into a single array using functions. If an array is short, a null value is added to the end of the function to match the length of the longer array.

SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); -- [{a, 1}, {b, 3}, {c, 5}]

SELECT zip_with(ARRAY[1, 2], ARRAY[3, 4], (x, y) -> x + y); -- [4, 6]

SELECT zip_with(ARRAY['a', 'b', 'c'], ARRAY['d', 'e', 'f'], (x, y) -> concat(x, y)); -- [ad, be, cf]

SELECT zip_with(ARRAY['a'], ARRAY['d', null, 'f'], (x, y) -> coalesce(x, y)); -- [a, null, f]

Parent topic: SQL Functions and Operators

Previous topic: Window Functions

Next topic: Map Functions and Operators