Collection Functions

Functions for working with arrays/lists, maps, and structs. Examples run in the shared demo; Copy includes any required model.

UNNEST

Expands an array or list into a set of rows, where each row contains one element from the array/list. This typically changes the grain of the query, hence the KEY purpose.

Inputs:

ARRAYLIST

Output:

Element type of the input collection

Purpose:

key

Example:

select unnest(['red', 'green', 'blue']) as color;

INDEX_ACCESS

Use array[index] to retrieve an element with a one-based integer index: array[1] is the first element. INDEX_ACCESS is the operation name, not a callable function. Out-of-bounds behavior depends on the backend.

Inputs:

ARRAY/LISTINTEGER

Output:

Element type of the input collection

Purpose:

property

Example:

const readings <- [10, 20, 30]; select readings[1] as first_reading;

MAP_ACCESS

Use map[key] to retrieve a value. MAP_ACCESS is the operation name, not a callable function. The key type must match the map's key type; missing-key behavior depends on the backend.

Inputs:

MAPINTEGER/STRING

Output:

Value type of the map

Purpose:

property

Example:

const preferences <- {'language': 'en', 'theme': 'dark'}; select preferences['language'] as language;

ATTR_ACCESS

Use struct.field to retrieve a named field. ATTR_ACCESS is the operation name, not a callable function. The field must exist in the declared struct type.

Inputs:

STRUCTSTRING

Output:

Type of the accessed attribute

Purpose:

property

Example:

const address <- struct('02110' -> zip_code, 'Boston' -> city); select address.zip_code as zip;

STRUCT

Constructs a record using value -> field_name bindings. Each field has its own type.

Inputs:

value -> field_name...

Output:

STRUCT with specified fields and types

Purpose:

property

Example:

select struct(101 -> id, 'ada@example.com' -> email) as user_info;

ARRAY

Use square brackets to construct an array from compatible values. ARRAY describes the type; use [a, b] in queries.

Inputs:

Any (compatible types)...

Output:

ARRAY of common compatible type of arguments

Purpose:

property

Example:

select ['red', 'blue'] as palette;

Array Literals

Square brackets build an array inline. With only constants, the array is a single fixed value. When any element is a concept, the array is built per row from that row's values, and it belongs to the same keys as those concepts.

select
    order_id,
    # one fixed value, the same on every row
    [1, 2, 3] as fixed_buckets,
    # a different array for each order
    [order_total, coalesce(order_total, 0.0)] as order_amounts;

Membership and Formatting

contains(array, value) tests membership, while the string overload tests for a substring. array_to_string accepts arrays of non-string values too:

select
    contains([1, 2, 3], 2) as has_two,
    array_to_string([1, 2, 3], ', ') as number_list;

The results are true and '1, 2, 3'. Numeric array elements do not need an explicit cast before formatting.

MAP_KEYS

Returns an array containing all the keys in the input map.

Inputs:

MAP

Output:

ARRAY

Purpose:

property

Example:

const preferences <- {'language': 'en', 'theme': 'dark'}; select map_keys(preferences) as property_names;

MAP_VALUES

Returns an array containing all the values in the input map.

Inputs:

MAP

Output:

ARRAY

Purpose:

property

Example:

const preferences <- {'language': 'en', 'theme': 'dark'}; select map_values(preferences) as property_values;

GENERATE_ARRAY

Generates an array from a start, end, and required step. The example returns [1, 3, 5, 7, 9]. Endpoint behavior can vary by backend.

Inputs:

INTEGERINTEGERINTEGER

Output:

ARRAY<INTEGER>

Purpose:

property

Example:

select generate_array(1, 10, 2) as odd_numbers;

ARRAY_DISTINCT

Returns an array with all duplicate values removed.

Inputs:

ARRAY

Output:

ARRAY

Purpose:

property

Example:

select array_distinct([1, 2, 2, 3]) as distinct_items;

ARRAY_SORT

Sorts array elements. The optional second argument is the unquoted keyword asc or desc; the default is ascending. Descending support depends on the backend.

Inputs:

ARRAYasc/desc (optional)

Output:

ARRAY

Purpose:

property

Example:

select array_sort([3, 1, 2], desc) as sorted_scores;

ARRAY_TRANSFORM

Applies a user defined function to each element of an array and returns a new array of the results. You must define the function prior to use.

Inputs:

ARRAYUSER_FUNCTION

Output:

ARRAY

Purpose:

property

Example:

def double(x) -> x * 2; select array_transform([1, 2, 3], @double) as doubled;

ARRAY_FILTER

Returns only the array elements for which a user-defined function returns true. Define the function first, then pass it with an @ prefix.

Inputs:

ARRAYUSER_FUNCTION

Output:

ARRAY

Purpose:

property

Example:

def greater_than_five(x) -> x > 5; select array_filter([2, 6, 8], @greater_than_five) as high_numbers;

ARRAY_TO_STRING

Converts an array to a string, with elements separated by a given delimiter.

Inputs:

ARRAYSTRING

Output:

STRING

Purpose:

property

Example:

select array_to_string(['red', 'blue'], ', ') as tag_string;

ARRAY_SUM

Computes the sum of all numeric elements in an array.

Inputs:

ARRAY<NUMERIC>

Output:

NUMERIC

Purpose:

property

Example:

select array_sum([10, 20, 30]) as order_total;