6.16. Array Functions and Operators
Subscript Operator: []
The []
operator is used to access an element of an array and is indexed starting from one:
- SELECT my_array[1] AS first_element
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 Functions
arraydistinct
(_x) → array
Remove duplicate values from the arrayx
.
arrayintersect
(_x, y) → array
Returns an array of the elements in the intersection ofx
andy
, without duplicates.
arrayunion
(_x, y) → array
Returns an array of the elements in the union ofx
andy
, without duplicates.
arrayexcept
(_x, y) → array
Returns an array of elements inx
but not iny
, without duplicates.
arrayjoin
(_x, delimiter, null_replacement) → varchar
Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.
arraymax
(_x) → x
Returns the maximum value of input array.
arraymin
(_x) → x
Returns the minimum value of input array.
arrayposition
(_x, element) → bigint
Returns the position of the first occurrence of theelement
in arrayx
(or 0 if not found).
arrayremove
(_x, element) → array
Remove all elements that equalelement
from arrayx
.
arraysort
(_x) → array
Sorts and returns the arrayx
. The elements ofx
must be orderable.Null elements will be placed at the end of the returned array.
arraysort
(_array<T>, function<T, T, int>) → array<T>
Sorts and returns thearray
based on the given comparatorfunction
. The comparator will taketwo nullable arguments representing two nullable elements of thearray
. It returns -1, 0, or 1as the first nullable element is less than, equal to, or greater than the second nullable element.If the comparator function returns other values (includingNULL
), the query will fail and raise an error- SELECT arraysort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); — [5, 3, 2, 2, 1]
SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); — ['dc', 'bc', 'ab']
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], — sort null first with descending order
(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); — [null, null, 5, 3, 2, 2, 1]
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], — sort null last with descending order
(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); — [5, 3, 2, 2, 1, null, null]
SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], — sort by string length
(x, y) -> IF(length(x) < length(y),
-1,
IF(length(x) = length(y), 0, 1))); — ['a', 'abc', 'abcd']
SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], — sort by array length
(x, y) -> IF(cardinality(x) < cardinality(y),
-1,
IF(cardinality(x) = cardinality(y), 0, 1))); — [[1, 2], [2, 3, 1], [4, 2, 1, 4]]
- SELECT arraysort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); — [5, 3, 2, 2, 1]
arrays_overlap
(_x, y) → boolean
Tests if arraysx
andy
have any any non-null elements in common.Returns null if there are no non-null elements in common but either array contains null.
cardinality
(x) → bigint
Returns the cardinality (size) of the arrayx
.
concat
(array1, array2, …, arrayN) → array
Concatenates the arraysarray1
,array2
,…
,arrayN
.This function provides the same functionality as the SQL-standard concatenation operator (||
).
contains
(x, element) → boolean
Returns true if the arrayx
contains theelement
.
elementat
(_array<E>, index) → E
Returns element ofarray
at givenindex
.Ifindex
> 0, this function provides the same functionality as the SQL-standard subscript operator ([]
).Ifindex
< 0,elementat
accesses elements from the last to the first.
filter
(_array<T>, function<T, boolean>) → array<T>
Constructs an array from those elements ofarray
for whichfunction
returns 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]
- SELECT filter(ARRAY [], x -> true); — []
flatten
(x) → array
Flattens anarray(array(T))
to anarray(T)
by concatenating the contained arrays.
reduce
(array<T>, initialState S, inputFunction<S, T, S>, outputFunction<S, R>) → R
Returns a single value reduced fromarray
.inputFunction
willbe invoked for each element inarray
in order. In addition to takingthe element,inputFunction
takes the current state, initiallyinitialState
, and returns the new state.outputFunction
will beinvoked to turn the final state into the result value. It may be theidentity 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], — calculates arithmetic average: 10.25
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));
- SELECT reduce(ARRAY [], 0, (s, x) -> s + x, s -> s); — 0
repeat
(element, count) → array
Repeatelement
forcount
times.
reverse
(x) → array
Returns an array which has the reversed order of arrayx
.
sequence
(start, stop) → array<bigint>
Generate a sequence of integers fromstart
tostop
, incrementingby1
ifstart
is less than or equal tostop
, otherwise-1
.
sequence
(start, stop, step) → array<bigint>
Generate a sequence of integers fromstart
tostop
, incrementing bystep
.
sequence
(start, stop) → array<date>
Generate a sequence of dates fromstart
date tostop
date, incrementingby1
day ifstart
date is less than or equal tostop
date, otherwise-1
day.
sequence
(start, stop, step) → array<date>
Generate a sequence of dates fromstart
tostop
, incrementing bystep
.The type ofstep
can be eitherINTERVAL DAY TO SECOND
orINTERVAL YEAR TO MONTH
.
sequence
(start, stop, step) → array<timestamp>
Generate a sequence of timestamps fromstart
tostop
, incrementing bystep
.The type ofstep
can be eitherINTERVAL DAY TO SECOND
orINTERVAL YEAR TO MONTH
.
shuffle
(x) → array
Generate a random permutation of the given arrayx
.
slice
(x, start, length) → array
Subsets arrayx
starting from indexstart
(or starting from the endifstart
is negative) with a length oflength
.
transform
(array<T>, function<T, U>) → array<U>
Returns an array that is the result of applyingfunction
to each element ofarray
:- 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]]
- SELECT transform(ARRAY [], x -> x + 1); — []
zip
(array1, array2[, …]) → array<row>
Merges the given arrays, element-wise, into a single array of rows. The M-th element ofthe N-th argument will be the N-th field of the M-th output element.If the arguments have an uneven length, missing values are filled withNULL
.- SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); — [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
- SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); — [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
zipwith
(_array<T>, array<U>, function<T, U, R>) → array<R>
Merges the two given arrays, element-wise, into a single array usingfunction
.If one array is shorter, nulls are appended at the end to match the length of the longer array, before applyingfunction
:- SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); — [ROW('a', 1), ROW('b', 3), ROW('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']
- SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); — [ROW('a', 1), ROW('b', 3), ROW('c', 5)]