Arrays, maps, and structs

ReferenceApplies to: Unified engine deployments

Nested values make it possible to carry related values in one expression. Input element and field types are resolved during planning, and backend support can be narrower than the function inventory.

Arrays

Function	Returns	Meaning
`array(value, ...)`, `make_array(value, ...)`	ARRAY	Construct an array with a common element type
`array_length(array)`	Integer	Number of elements
`array_contains(array, value)`	BOOLEAN	Test membership under the resolved function’s null rules
`array_distinct(array)`	ARRAY	Remove repeated elements
`array_sort(array)`	ARRAY	Sort array elements using supported ordering

SELECT i_item_sk,
       ARRAY_LENGTH(ARRAY(i_category, i_class)) AS label_slots
FROM demo.tpcds.item
ORDER BY i_item_sk
LIMIT 3;

Structs and maps

struct(column, ...) derives field names from column arguments. named_struct(name, value, ...) names fields explicitly. get_field(struct, field) and alias getfield access a named field. map(key, value, ...) requires alternating key/value arguments.

SELECT i_item_sk,
       GET_FIELD(STRUCT(i_item_sk, i_item_id), 'i_item_id') AS item_code
FROM demo.tpcds.item
ORDER BY i_item_sk
LIMIT 3;

Higher-order functions

transform(array, x -> expression) maps elements. filter(array, x -> predicate) keeps matching elements. exists(array, x -> predicate) tests whether any element matches. Their canonical registry names are array_transform, array_filter, and array_any_match. The lambda variable is local to its expression, and output types depend on the lambda result.

SELECT ARRAY_LENGTH(TRANSFORM(ARRAY('books', 'music'), x -> UPPER(x))) AS mapped_count,
       ARRAY_LENGTH(FILTER(ARRAY('books', 'music'), x -> x = 'books')) AS matching_count,
       EXISTS(ARRAY('books', 'music'), x -> x = 'books') AS has_books;

Native result transport rejects some nested ARRAY/STRUCT output columns, so these examples return scalar values. ANSI arithmetic inside lambdas can be rejected when numeric types are unresolved. Nested query expressions and nested connector writes are separate capabilities. If a nested type can be returned by SELECT, confirm that your table format and write operation also accept it before inserting it.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Arrays, maps, and structs

Arrays

Structs and maps

Higher-order functions

​Arrays

​Structs and maps

​Higher-order functions

Arrays

Structs and maps

Higher-order functions