Array Functions

`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>

🔗 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

`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

🔗 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

🔗 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. Supported Signatures

function element_at(array<$3>, bigint) returns $3

🔗 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

`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

SDF DB

Trino Supported Functions

Array Functions

`array_distinct`

`array_except`

`array_intersect`

`array_join`

`array_max`

`array_min`

`array_position`

`array_remove`

`array_sort`

`array_union`

`cardinality`

`contains`

`element_at`

`flatten`

`repeat`

​array_distinct

​array_except

​array_intersect

​array_join

​array_max

​array_min

​array_position

​array_remove

​array_sort

​array_union

​cardinality

​contains

​element_at

​flatten

​repeat

`array_distinct`

`array_except`

`array_intersect`

`array_join`

`array_max`

`array_min`

`array_position`

`array_remove`

`array_sort`

`array_union`

`cardinality`

`contains`

`element_at`

`flatten`

`repeat`