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#
- all_match(array(T), function(T, boolean)) boolean#
- Returns whether all elements of an array match the given predicate. Returns - trueif all the elements match the predicate (a special case is when the array is empty);- falseif one or more elements don’t match;- NULLif the predicate function returns- NULLfor one or more elements and- truefor all other elements.
- any_match(array(T), function(T, boolean)) boolean#
- Returns whether any elements of an array match the given predicate. Returns - trueif one or more elements match the predicate;- falseif none of the elements matches (a special case is when the array is empty);- NULLif the predicate function returns- NULLfor one or more elements and- falsefor all other elements.
- 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 - xand- y, without duplicates.
- array_union(x, y) array#
- Returns an array of the elements in the union of - xand- y, without duplicates.
- array_except(x, y) array#
- Returns an array of elements in - xbut not in- y, without duplicates.
- array_histogram(x) map<K, bigint>#
- Returns a map where the keys are the unique elements in the input array - xand the values are the number of times that each element appears in- x. Null values are ignored.- SELECT array_histogram(ARRAY[42, 7, 42, NULL]); -- {42=2, 7=1} - Returns an empty map if the input array has no non-null elements. - SELECT array_histogram(ARRAY[NULL, NULL]); -- {} 
- array_join(x, delimiter) varchar#
- Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result. 
- 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 - elementin array- x(or 0 if not found).
- array_remove(x, element) array#
- Remove all elements that equal - elementfrom array- x.
- array_sort(x) array#
- Sorts and returns the array - x. The elements of- xmust be orderable. Null elements will be placed at the end of the returned array.
- array_sort(array(T), function(T, T, int)) -> array(T)
- Sorts and returns the - arraybased on the given comparator- function. The comparator will take two nullable arguments representing two nullable elements of the- array. It returns -1, 0, or 1 as the first nullable element is less than, equal to, or greater than the second nullable element. If the comparator function returns other values (including- NULL), the query will fail and raise an error.- SELECT array_sort(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]] 
- arrays_overlap(x, y) boolean#
- Tests if arrays - xand- yhave 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 (- ||).
- combinations(array(T), n) -> array(array(T))#
- Returns n-element sub-groups of input array. If the input array has no duplicates, - combinationsreturns n-element subsets.- 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]] - Order of sub-groups is deterministic but unspecified. Order of elements within a sub-group deterministic but unspecified. - nmust be not be greater than 5, and the total size of sub-groups generated must be smaller than 100,000.
- contains(x, element) boolean#
- Returns true if the array - xcontains the- element.
- contains_sequence(x, seq) boolean#
- Return true if array - xcontains all of array- seqas a subsequence (all values in the same consecutive order).
- element_at(array(E), index) E#
- Returns element of - arrayat given- index. If- index> 0, this function provides the same functionality as the SQL-standard subscript operator (- []), except that the function returns- NULLwhen accessing an- indexlarger than array length, whereas the subscript operator would fail in such a case. If- index< 0,- element_ataccesses elements from the last to the first.
- filter(array(T), function(T, boolean)) -> array(T)#
- Constructs an array from those elements of - arrayfor which- functionreturns 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 an- array(T)by concatenating the contained arrays.
- ngrams(array(T), n) -> array(array(T))#
- Returns - n-grams (sub-sequences of adjacent- nelements) for the- array. The order of the- n-grams in the result is unspecified.- 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)) boolean#
- Returns whether no elements of an array match the given predicate. Returns - trueif none of the elements matches the predicate (a special case is when the array is empty);- falseif one or more elements match;- NULLif the predicate function returns- NULLfor one or more elements and- falsefor all other elements.
- reduce(array(T), initialState S, inputFunction(S, T, S), outputFunction(S, R)) R#
- Returns a single value reduced from - array.- inputFunctionwill be invoked for each element in- arrayin order. In addition to taking the element,- inputFunctiontakes the current state, initially- initialState, and returns the new state.- outputFunctionwill 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], BIGINT '0', (s, x) -> s + x, s -> s); -- 2147483648 -- calculates arithmetic average 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) array#
- Repeat - elementfor- counttimes.
- reverse(x) array
- Returns an array which has the reversed order of array - x.
- sequence(start, stop)#
- Generate a sequence of integers from - startto- stop, incrementing by- 1if- startis less than or equal to- stop, otherwise- -1.
- sequence(start, stop, step)
- Generate a sequence of integers from - startto- stop, incrementing by- step.
- sequence(start, stop)
- Generate a sequence of dates from - startdate to- stopdate, incrementing by- 1day if- startdate is less than or equal to- stopdate, otherwise- -1day.
- sequence(start, stop, step)
- Generate a sequence of dates from - startto- stop, incrementing by- step. The type of- stepcan be either- INTERVAL DAY TO SECONDor- INTERVAL YEAR TO MONTH.
- sequence(start, stop, step)
- Generate a sequence of timestamps from - startto- stop, incrementing by- step. The type of- stepcan be either- INTERVAL DAY TO SECONDor- INTERVAL YEAR TO MONTH.
- shuffle(x) array#
- Generate a random permutation of the given array - x.
- slice(x, start, length) array#
- Subsets array - xstarting from index- start(or starting from the end if- startis negative) with a length of- length.
- trim_array(x, n) array#
- Remove - nelements from the end of 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)#
- Returns an array that is the result of applying - functionto each element of- 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)#
- 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. If one array is shorter, nulls are appended at the end to match the length of the longer array, before applying- function.- 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']