Skip to main content

​
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
🔗 Official Documentation

​
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
🔗 Official Documentation

​
array_distinct

Remove duplicate values from the array x. Examples:
examples.sql
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>
🔗 Official Documentation

​
array_except

Returns an array of elements in x but not in y, without duplicates. Examples:
examples.sql
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>
🔗 Official Documentation

​
array_intersect

Returns an array of the elements in the intersection of x and y, without duplicates. Examples:
examples.sql
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>
🔗 Official Documentation

​
array_join

Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result. Examples:
examples.sql
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
🔗 Official Documentation

​
array_max

Returns the maximum value of input array. Supported Signatures 
function array_max(array<$1>) returns $1
🔗 Official Documentation

​
array_min

Returns the minimum value of input array. Supported Signatures 
function array_min(array<$1>) returns $1
🔗 Official Documentation

​
array_position

Returns the position of the first occurrence of the element in array x (or 0 if not found). Examples:
examples.sql
SELECT array_position([1, 1, 2], 1) AS value; -- value '1'
Supported Signatures 
function array_position(array<$1>, $1) returns bigint
🔗 Official Documentation

​
array_remove

Remove all elements that equal element from array x. Examples:
examples.sql
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>
🔗 Official Documentation

​
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>
🔗 Official Documentation

​
array_union

Returns an array of the elements in the union of x and y, without duplicates. Examples:
examples.sql
SELECT array_union([1, 1], [2]) as value; -- value '[1, 2]'
Supported Signatures 
function array_union(array<$3>, array<$3>) returns array<$3>
🔗 Official Documentation

​
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
🔗 Official Documentation

​
cardinality

Returns the cardinality (size) of the array x. Examples:
examples.sql
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
🔗 Official Documentation

​
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>>
🔗 Official Documentation

​
contains

Takes an array and an element. Returns true if the array contains the element, false if not. Examples:
examples.sql
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
🔗 Official Documentation

​
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
🔗 Official Documentation

​
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:
examples.sql
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
🔗 Official Documentation

​
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>
🔗 Official Documentation

​
flatten

Flattens an array(array(T)) to an array(T) by concatenating the contained arrays. Examples:
examples.sql
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>
🔗 Official Documentation

​
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>>
🔗 Official Documentation

​
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
🔗 Official Documentation

​
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
🔗 Official Documentation

​
repeat

Repeat element for count times. Examples:
examples.sql
select repeat('😉', 4) as value; -- value '😉😉😉😉'
Supported Signatures 
function repeat($1, bigint) returns array<$1>
🔗 Official Documentation

​
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)>
🔗 Official Documentation

​
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. 🔗 Official Documentation

​
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>
🔗 Official Documentation

​
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>
🔗 Official Documentation

​
trim_array

Remove n elements from the end of array. Supported Signatures 
function trim_array(array<$3>, bigint) returns array<$3>
🔗 Official Documentation

​
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)>
🔗 Official Documentation

​
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>
🔗 Official Documentation
⌘I