Array Functions
all_match
Returns whether all elements of an array match the given predicate. Returns true if all the elements match the predicate (a special case is when the array is empty); false if one or more elements don’t match; NULL if the predicate function returns NULL for one or more elements and true for all other elements.
Supported Signatures
function all_match(array<$1>, function($1, boolean)) returns boolean
any_match
Returns whether any elements of an array match the given predicate. Returns true if one or more elements match the predicate; false if none of the elements matches (a special case is when the array is empty); NULL if the predicate function returns NULL for one or more elements and false for all other elements.
Supported Signatures
function any_match(array<$1>, function($1, boolean)) returns boolean
array_distinct
Remove duplicate values from the array x.
Examples:
SELECT array_distinct([1, 2, 3]) as value; -- value '[1, 2, 3]'
SELECT array_distinct([1, 1, 2, 3]) as value; -- value '[1, 2, 3]'
Supported Signatures
function array_distinct(array<$3>) returns array<$3>
array_except
Returns an array of elements in x but not in y, without duplicates.
Examples:
SELECT array_except([1, 1, 2, 3], [1]) AS value; -- value '[2, 3]'
Supported Signatures
function array_except(array<$3>, array<$3>) returns array<$3>
array_intersect
Returns an array of the elements in the intersection of x and y, without duplicates.
Examples:
SELECT array_intersect([1, 1, 2, 3], [1]) AS value; -- value '[1]'
SELECT array_intersect(['hello', 'world'], ['hello']) AS value; -- value '[hello]'
Supported Signatures
function array_intersect(array<$3>, array<$3>) returns array<$3>
array_join
Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result.
Examples:
SELECT array_join(['hello', 'world'], ' beautiful ') AS value; -- value 'hello beautiful world'
Supported Signatures
function array_join(array<$1>, varchar) returns varchar
function array_join(array<$1>, varchar, varchar) returns varchar
array_max
Returns the maximum value of input array.
Supported Signatures
function array_max(array<$1>) returns $1
array_min
Returns the minimum value of input array.
Supported Signatures
function array_min(array<$1>) returns $1
array_position
Returns the position of the first occurrence of the element in array x (or 0 if not found).
Examples:
SELECT array_position([1, 1, 2], 1) AS value; -- value '1'
Supported Signatures
function array_position(array<$1>, $1) returns bigint
array_remove
Remove all elements that equal element from array x.
Examples:
SELECT array_remove([1, 1, 2], 1) AS value; -- value '[2]'
SELECT array_remove(['hello', 'bad', 'world'], 'bad') AS value; -- value '[hello, world]'
Supported Signatures
function array_remove(array<$3>, $3) returns array<$3>
array_sort
Sorts and returns the array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array.
Supported Signatures
function array_sort(array<$3>) returns array<$3>
function array_sort(array<$1>, function($1, $1, bigint)) returns array<$1>
array_union
Returns an array of the elements in the union of x and y, without duplicates.
Examples:
SELECT array_union([1, 1], [2]) as value; -- value '[1, 2]'
Supported Signatures
function array_union(array<$3>, array<$3>) returns array<$3>
arrays_overlap
Tests if arrays x and y have any non-null elements in common. Returns null if there are no non-null elements in common but either array contains null.
Supported Signatures
function arrays_overlap(array<$3>, array<$3>) returns boolean
cardinality
Returns the cardinality (size) of the array x.
Examples:
SELECT cardinality([1, 1, 2]) as value; -- value '3'
Supported Signatures
function cardinality(array<$3>) returns bigint
function cardinality(hyperloglog) returns bigint
function cardinality(map<$4, $5>) returns bigint
function cardinality(setdigest) returns bigint
combinations
Returns n-element sub-groups of input array. If the input array has no duplicates, combinations returns n-element subsets.
Supported Signatures
function combinations(array<$1>, bigint) returns array<array<$1>>
contains
Takes an array and an element. Returns true if the array contains the element, false if not.
Examples:
SELECT contains(['hello', 'world'], 'hello') AS value; -- value 'true'
SELECT contains(['hello', 'world'], '😊') AS value; -- value 'false'
Supported Signatures
function contains(array<$1>, $1) returns boolean
function contains(varchar, ipaddress) returns boolean
contains_sequence
Return true if array x contains all of array seq as a subsequence (all values in the same consecutive order).
Supported Signatures
function contains_sequence(array<$1>, array<$1>) returns boolean
element_at
Returns element of array at given index. If index > 0, this function provides the same functionality as the SQL-standard subscript operator ([]), except that the function returns NULL when accessing an index larger than array length, whereas the subscript operator would fail in such a case. If index < 0, element_at accesses elements from the last to the first.
Examples:
SELECT element_at([3, 5, 7], 2) AS value; -- value '5'
Supported Signatures
function element_at(map<$4, $5>, $4) returns $5
function element_at(array<$3>, bigint) returns $3
filter
Constructs an array from those elements of array for which function returns true.
Supported Signatures
function filter(array<$1>, function($1, boolean)) returns array<$1>
flatten
Flattens an array(array(T)) to an array(T) by concatenating the contained arrays.
Examples:
SELECT flatten(
ARRAY [ARRAY [1, 2],
ARRAY [3, 4]])
AS value;
-- value '[1, 2, 3, 4]'
Supported Signatures
function flatten(array<array<$3>>) returns array<$3>
ngrams
Returns n-grams (sub-sequences of adjacent n elements) for the array. The order of the n-grams in the result is unspecified.
Supported Signatures
function ngrams(array<$1>, bigint) returns array<array<$1>>
none_match
Returns whether no elements of an array match the given predicate. Returns true if none of the elements matches the predicate (a special case is when the array is empty); false if one or more elements match; NULL if the predicate function returns NULL for one or more elements and false for all other elements.
Supported Signatures
function none_match(array<$1>, function($1, boolean)) returns boolean
reduce
Returns a single value reduced from array. inputFunction will be invoked for each element in array in order. In addition to taking the element, inputFunction takes the current state, initially initialState, 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).
Supported Signatures
function reduce(array<$1>, $10, function($10, $1, $10), function($10, $9)) returns $9
repeat
Repeat element for count times.
Examples:
select repeat('😉', 4) as value; -- value '😉😉😉😉'
Supported Signatures
function repeat($1, bigint) returns array<$1>
sequence
Generate a sequence of integers from start to stop, incrementing by 1 if start is less than or equal to stop, otherwise -1.
Supported Signatures
function sequence(bigint, bigint) returns array<bigint>
function sequence(bigint, bigint, bigint) returns array<bigint>
function sequence(date, date) returns array[date]
function sequence(date, date, interval day to second) returns array[date]
function sequence(date, date, interval year to month) returns array[date]
function sequence(timestamp(p), timestamp(p), interval day to second) returns array<timestamp(p)>
shuffle
Generate a random permutation of the given array x.
Supported Signatures
function shuffle(array<$3>) returns array<$3>
Note: shuffle() is volatile, i.e. might return a different value for the same input.
slice
Subsets array x starting from index start (or starting from the end if start is negative) with a length of length.
Supported Signatures
function slice(array<$3>, bigint, bigint) returns array<$3>
transform
Returns an array that is the result of applying function to each element of array.
Supported Signatures
function transform(array<$1>, function($1, $11)) returns array<$11>
trim_array
Remove n elements from the end of array.
Supported Signatures
function trim_array(array<$3>, bigint) returns array<$3>
zip
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.
Supported Signatures
function zip(array<$14>, array<$15>) returns array<row(c0 $14, c1 $15)>
function zip(array<$14>, array<$15>, array<$16>) returns array<row(c0 $14, c1 $15, c2 $16)>
function zip(array<$14>, array<$15>, array<$16>, array<$17>) returns array<row(c0 $14, c1 $15, c2 $16, c3 $17)>
function zip(array<$14>, array<$15>, array<$16>, array<$17>, array<$18>) returns array<row(c0 $14, c1 $15, c2 $16, c3 $17, c4 $18)>
zip_with
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.
Supported Signatures
function zip_with(array<$1>, array<$11>, function($1, $11, $9)) returns array<$9>