Collection Functions

Functions for working with complex data types like arrays/lists, maps, and structs.

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 order_id, UNNEST(product_ids) AS product_id;

INDEX_ACCESS

Retrieves an element from an array or list using its zero-based integer index. Accessing an index outside the bounds may result in NULL or an error.

Inputs:

ARRAY/LISTINTEGER

Output:

Element type of the input collection

Purpose:

property

Example:

SELECT INDEX_ACCESS(sensor_readings, 0) AS first_reading;

MAP_ACCESS

Retrieves the value associated with a specified key from a map. The key type must match the map's key type (usually STRING or INTEGER). Returns NULL if the key is not found.

Inputs:

MAPINTEGER/STRING

Output:

Value type of the map

Purpose:

property

Example:

SELECT MAP_ACCESS(user_properties, 'preferred_language') AS language;

ATTR_ACCESS

Retrieves the value of a named field (attribute) from a struct. The second argument is the attribute name (string). Accessing a non-existent attribute may result in NULL or an error.

Inputs:

STRUCTSTRING

Output:

Type of the accessed attribute

Purpose:

property

Example:

SELECT ATTR_ACCESS(address_struct, 'zip_code') AS zip;

STRUCT

Constructs a struct (similar to a record or object) dynamically from pairs of arguments: field name (string literal) followed by the field value.

Inputs:

STRING (name)Any (value)...

Output:

STRUCT with specified fields and types

Purpose:

property

Example:

SELECT STRUCT('id', user_id, 'email', user_email) AS user_info;

ARRAY

Constructs an array containing the provided arguments. All arguments should ideally be of a compatible type; behavior/support depends on target DB. The output type depends on the input argument types.

Inputs:

Any (compatible types)...

Output:

ARRAY of common compatible type of arguments

Purpose:

property

Example:

SELECT ARRAY(primary_color, secondary_color) 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
    orders.id,
    # one fixed value, the same on every row
    [1, 2, 3] as fixed_buckets,
    # a different array for each order
    [orders.revenue, orders.cost] 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:

SELECT MAP_KEYS(user_properties) AS property_names;

MAP_VALUES

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

Inputs:

MAP

Output:

ARRAY

Purpose:

property

Example:

SELECT MAP_VALUES(user_properties) AS property_values;

GENERATE_ARRAY

Generates an array of numbers in a given range, with an optional step.

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(items) AS distinct_items;

ARRAY_SORT

Sorts the elements of an array. The second argument specifies the sort order ('asc' or 'desc').

Inputs:

ARRAYSTRING

Output:

ARRAY

Purpose:

property

Example:

SELECT ARRAY_SORT(scores, '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(numbers, @double) as doubled;

ARRAY_FILTER

Filters an array by applying a function to each element and, returning a new array with only the elements for which the function returns true. You must define the function prior to use.

Inputs:

ARRAYLAMBDA

Output:

ARRAY

Purpose:

property

Example:

def greater_than_five(x)-> x>5; SELECT ARRAY_FILTER(numbers, @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(tags, ', ') 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(line_item_totals) AS order_total;