Handle arrays of rows

Here's an overview of working with arrays of ROW types in FeatureQL.

Arrays of rows aren't a distinct type, but because they're central to entity centered modeling, FeatureQL has dedicated functions for working with them.

You can see the full list of array of rows functions in the Functions page.

Operations on fields

Operation	Syntax	Description
Access	`ORDERS[amount]`	Extract one field from an array of rows as a array of values (can also extract multiple fields as an array of ROWs)
Zip	`ZIP(<array_of_rows1>, <array_of_rows2>)`	Zips two arrays as an array of rows (can also zip a single array to get a array of rows)
Combine	`ARRAY_MERGE(<array_of_rows1>, <array_of_rows2>)`	Combines array of rows
Unwrap	`UNWRAP(<array_of_rows>)`	Unwraps an array of rows with one field as a array of values (convenient when the name of the field is not named)
Transform	`TRANSFORM(<array_of_rows>, <subquery>)`	Applies transformations to an array of rows (map, filter, aggregate)
Enrich	`ADD_FIELDS()`	Adds fields to array of rows by joining on an existing field

Note: Operations on fields are similar to operations on ROW except for ZIP and TRANSFORM.

Operations on rows

Operation	Syntax	Description
Extract	`ORDERS[1]` or `ORDERS[1:3]`	Get the first or first to third row as a array of rows
Concatenate	`ARRAY_CONCAT(<array_of_rows1>, <array_of_rows2>)`	Concatenates the rows of the two arrays of rows (we can also deduplicate on a field)

Note: Operations on rows are similar to operations on ARRAY.

literals

Extractions

Concatenate two arrays of rows

Concatenate with optional deduplication on a field value.

Transform: map, filter, reduce as a subquery

The TRANSFORM() function allows you to treat arrays of rows like mini tables and manipulate them using familiar FeatureQL syntax.

Here's the general structure:

TRANSFORM(
    base
    USING (
        WITH [...internal field definitions]
        SELECT [...fields to return including group bys]
        WHERE ... [...final filter, equivalent of having]
        ORDER BY ...
        LIMIT ...
    )
    IMPORTING import_1, import_2...
)

sql

The transformation query must be self contained.

You cannot:

Join with external tables or fields, so no ADD_FIELDS()
Reference data outside of the base array
Nest other transforms()

If you need to incorporate external data, add it to your array of rows with ADD_FIELDS() before applying TRANSFORM().

Example usage

Here are examples demonstrating practical cases:

Suggest changes to this page

Last update at: 2025/11/06 07:00:15

On this page

literals Extractions Concatenate two arrays of rows Transform: map, filter, reduce as a subquery Example usage