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
-
array_distinct
(x) → array Remove duplicate values from the array
x
.
-
array_intersect
(x, y) → array Returns an array of the elements in the intersection of
x
andy
, without duplicates.
-
array_union
(x, y) → array Returns an array of the elements in the union of
x
andy
, without duplicates.
-
array_except
(x, y) → array Returns an array of elements in
x
but not iny
, without duplicates.
-
array_join
(x, delimiter, null_replacement) → varchar Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.
-
array_max
(x) → x Returns the maximum value of input array.
-
array_min
(x) → x Returns the minimum value of input array.
-
array_position
(x, element) → bigint Returns the position of the first occurrence of the
element
in arrayx
(or 0 if not found).
-
array_remove
(x, element) → array Remove all elements that equal
element
from arrayx
.
-
array_sort
(x) → array Sorts and returns the array
x
. The elements ofx
must be orderable. Null elements will be placed at the end of the returned array.
-
arrays_overlap
(x, y) → boolean Tests if arrays
x
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 array
x
.
-
concat
(array1, array2, ..., arrayN) → array Concatenates the arrays
array1
,array2
,...
,arrayN
. This function provides the same functionality as the SQL-standard concatenation operator (||
).
-
contains
(x, element) → boolean Returns true if the array
x
contains theelement
.
-
element_at
(array<E>, index) → E Returns element of
array
at givenindex
. Ifindex
> 0, this function provides the same functionality as the SQL-standard subscript operator ([]
). Ifindex
< 0,element_at
accesses elements from the last to the first.
-
filter
(array<T>, function<T, boolean>) → array<T> Constructs an array from those elements of
array
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]
-
flatten
(x) → array Flattens an
array(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 from
array
.inputFunction
will be invoked for each element inarray
in order. In addition to taking the element,inputFunction
takes the current state, initiallyinitialState
, and returns the new state.outputFunction
will be invoked to turn the final state into the result value. It may be the identity 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));
-
repeat
(element, count) → array Repeat
element
forcount
times.
-
reverse
(x) → array Returns an array which has the reversed order of array
x
.
-
sequence
(start, stop) → array<bigint> Generate a sequence of integers from
start
tostop
, incrementing by1
ifstart
is less than or equal tostop
, otherwise-1
.
-
sequence
(start, stop, step) → array<bigint> Generate a sequence of integers from
start
tostop
, incrementing bystep
.
-
sequence
(start, stop) → array<date> Generate a sequence of dates from
start
date tostop
date, incrementing by1
day ifstart
date is less than or equal tostop
date, otherwise-1
day.
-
sequence
(start, stop, step) → array<date> Generate a sequence of dates from
start
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 from
start
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 array
x
.
-
slice
(x, start, length) → array Subsets array
x
starting from indexstart
(or starting from the end ifstart
is negative) with a length oflength
.
-
transform
(array<T>, function<T, U>) → array<U> Returns an array that is the result of applying
function
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]]
-
zip
(array1, array2[, ...]) → array<row> Merges the given arrays, element-wise, into a single array of rows. The M-th element of the 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 with
NULL
.SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
-
zip_with
(array<T>, array<U>, function<T, U, R>) → array<R> Merges the two given arrays, element-wise, into a single array using
function
. Both arrays must be the same length.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']