Array Functions and Operators

Array indexing.

Example:

db> 

SELECT [1, 2, 3][0];

{1}

db> 

SELECT [(x := 1, y := 1), (x := 2, y := 3.3)][1];

{(x := 2, y := 3.3)}

Negative indexing is supported:

db> 

SELECT [1, 2, 3][-1];

{3}

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

db> 

SELECT [1, 2, 3][4];

InvalidValueError: array index 4 is out of bounds

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:

db> 

SELECT [1, 2, 3][0:2];

{[1, 2]}

db> 

SELECT [1, 2, 3][2:];

{[3]}

db> 

SELECT [1, 2, 3][:1];

{[1]}

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

db> 

SELECT [1, 2, 3][1:20];

{[2, 3]}

db> 

SELECT [1, 2, 3][10:20];

{[]}

Array concatenation.

db> 

SELECT [1, 2, 3] ++ [99, 98];

{[1, 2, 3, 99, 98]}

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

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

db> 

SELECT array_agg({2, 3, 5});

{[2, 3, 5]}

db> 

SELECT array_agg(User.name ORDER BY User.name);

{['Alice', 'Bob', 'Joe', 'Sam']}

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.

db> 

SELECT array_get([2, 3, 5], 1);

{3}

db> 

SELECT array_get([2, 3, 5], 100);

{}

db> 

SELECT array_get([2, 3, 5], 100, default := 42);

{42}

Return array elements as a set.

db> 

SELECT array_unpack([2, 3, 5]);

{3, 2, 5}

Render an array to a string.

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

db> 

SELECT to_str(['one', 'two', 'three'], ', ');

{'one, two, three'}