Array Functions and Operators

array[i]

Array indexing.

array[from:to]

Array slicing.

array ++ array

Array concatenation.

array = array, array < array, …

Comparison operators.

len()

Return number of elements in the array.

contains()

Check if an element is in the array.

find()

Find the index of an element in the array.

array_join()

Render an array to a string.

array_agg()

Return an array made from all of the input set elements.

array_get()

Return the element of array at the specified index.

array_unpack()

Return array elements as a set.

operator
array[i]
array<anytype> [ int64 ] -> anytype

Array indexing.

Example:

Copy
db> 
SELECT [1, 2, 3][0];
{1}
Copy
db> 
SELECT [(x := 1, y := 1), (x := 2, y := 3.3)][1];
{(x := 2, y := 3.3)}

Negative indexing is supported:

Copy
db> 
SELECT [1, 2, 3][-1];
{3}

Referencing a non-existent array element will result in an error:

Copy
db> 
SELECT [1, 2, 3][4];
InvalidValueError: array index 4 is out of bounds
operator
array[from:to]
array<anytype> [ int64 : int64 ] -> anytype

Array slicing.

An omitted lower bound defaults to zero, and an omitted upper bound defaults to the size of the array.

The upper bound is non-inclusive.

Examples:

Copy
db> 
SELECT [1, 2, 3][0:2];
{[1, 2]}
Copy
db> 
SELECT [1, 2, 3][2:];
{[3]}
Copy
db> 
SELECT [1, 2, 3][:1];
{[1]}
Copy
db> 
SELECT [1, 2, 3][:-2];
{[1]}

Referencing an array slice beyond the array boundaries will result in an empty array (unlike a direct reference to a specific index):

Copy
db> 
SELECT [1, 2, 3][1:20];
{[2, 3]}
Copy
db> 
SELECT [1, 2, 3][10:20];
{[]}
operator
array ++ array
array<anytype> ++ array<anytype> -> array<anytype>

Array concatenation.

Copy
db> 
SELECT [1, 2, 3] ++ [99, 98];
{[1, 2, 3, 99, 98]}
function
array_agg()
std::array_agg(s: SET OF anytype) -> array<anytype>

Return an array made from all of the input set elements.

The ordering of the input set will be preserved if specified.

Copy
db> 
SELECT array_agg({2, 3, 5});
{[2, 3, 5]}
Copy
db> 
SELECT array_agg(User.name ORDER BY User.name);
{['Alice', 'Bob', 'Joe', 'Sam']}
function
array_get()
std::array_get(array: array<anytype>, index: int64, NAMED ONLY default: anytype = {} ) -> OPTIONAL anytype

Return the element of array at the specified index.

If index is out of array bounds, the default or {} (empty set) is returned.

This works the same as array indexing operator except that if the index is outside array boundaries an empty set of the array element type is returned instead of raising an exception.

Copy
db> 
SELECT array_get([2, 3, 5], 1);
{3}
Copy
db> 
SELECT array_get([2, 3, 5], 100);
{}
Copy
db> 
SELECT array_get([2, 3, 5], 100, default := 42);
{42}
function
array_unpack()
std::array_unpack(array: array<anytype>) -> SET OF anytype

Return array elements as a set.

The ordering of the returned set is not guaranteed.

Copy
db> 
SELECT array_unpack([2, 3, 5]);
{3, 2, 5}
function
array_join()
std::array_join(array: array<str>, delimiter: str) -> str

Render an array to a string.

Join a string array into a single string using a specified delimiter:

Copy
db> 
SELECT to_str(['one', 'two', 'three'], ', ');
{'one, two, three'}
Light
Dark
System