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

🔗 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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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