Array of rows overview

FeatureQL is entity-centric: each row in a query represents one entity (a customer, a product, an account). When an entity has many related facts — a customer's orders, a product's price history — those facts are stored as an array of rows inside the entity row rather than in a separate table.

This is the core data structure behind Entity Centric Modeling (ECM). Instead of joining across tables at query time, you keep everything nested within the entity and operate on it directly.

FeatureQL provides a rich set of operations for working with arrays of rows:

The full list of array functions is available in the Functions reference.

Compatibility with generic array functions

Most generic array functions accept arrays of rows. The following work directly on ARRAY<ROW> inputs: ARRAY_COUNT, ARRAY_DISTINCT, ARRAY_ENUMERATE, ARRAY_REVERSE, ARRAY_SORT, ARRAY_REMOVE, ARRAY_UNION, ARRAY_INTERSECT, ARRAY_EXCEPT, ARRAY_MIN, ARRAY_MAX, CONTAINS, REPEAT, ARRAY_POSITION, ELEMENT_AT_POS, ELEMENTS_AT_POS, ARRAYS_OVERLAP, CONTAINS_SEQUENCE, and FLATTEN.

When using the two-array set operations (ARRAY_UNION, ARRAY_INTERSECT, ARRAY_EXCEPT) or CONTAINS, both arrays — or the array and the search element — must share the exact same row schema (same field names and types). FeatureQL validates this at compile time and rejects mismatches with a clear error.

The following functions require scalar element types and will reject ARRAY<ROW> inputs: ARRAY_SUM, ARRAY_AVG, ARRAY_PRODUCT, ARRAY_MEDIAN, ARRAY_STDDEV_POP, ARRAY_JOIN, ARRAY_NOT, NORMALIZE, COSINE_SIMILARITY, ARRAY_ROTATE, and ARRAY_HISTOGRAM.

Creating arrays of rows

There are several ways to construct an array of rows. You can build it from ROW() literals, use ZIP() to combine separate arrays, or cast unnamed rows to give them field names.

SELECT
    unnamed := ARRAY(ROW('D', 'E', 'F'), ROW('G', 'H', 'I')),
    named := ARRAY(ROW('D' AS col1, 'E' AS col2), ROW('G' AS col1, 'H' AS col2)),
    renamed := ARRAY(ROW(4, 'A'), ROW(5, 'B'), ROW(6, 'C'))::ARRAY(
        ROW(num BIGINT, str VARCHAR)
    )
;

Accessing fields and rows

Use bracket notation to slice into an array of rows. A numeric index selects rows; a field name selects columns. You can chain both — for example, first pick specific rows, then extract a field — in either order.

SELECT
    base := ARRAY(ROW(1, 'A'), ROW(2, 'B'), ROW(3, 'C'), ROW(4, 'D'))::ARRAY(
        ROW(col1 BIGINT, col2 VARCHAR)
    ),
    single_row := base[2],
    multiple_rows := base[2, 4],
    range_rows := base[2:4],
    multiple_fields := base[col2, col1],
    multiple_fields_renamed := base[col2 AS field2, col1 AS field1],
    field_then_row := base[col2][1, 3],
    row_then_field := base[1, 3][col2]
;

Concatenating arrays of rows

ARRAY_CONCAT appends the rows of two arrays. When combining data from different sources, use DEDUPLICATED ON to keep only one row per key value. Rows from the second array take priority.

WITH
    base1 := ARRAY(ROW(1, 'A', 'u1'), ROW(2, 'B', 'u2a'))::ARRAY(
        ROW(col1 BIGINT, col2 VARCHAR, col3 VARCHAR)
    ),
    base2 := ARRAY(ROW(2, 'BB', 'u2b'), ROW(2, 'BB', 'u2c'), ROW(3, 'CC', 'u3'))::ARRAY(
        ROW(col1 BIGINT, col2 VARCHAR, col3 VARCHAR)
    )
SELECT
    no_dedup := ARRAY_CONCAT(base1, base2),
    dedup_on_col1 := ARRAY_CONCAT(base1, base2 DEDUPLICATE ON col1),
    dedup_on_col1_and_col2 := ARRAY_CONCAT(
        base1,
        base2 DEDUPLICATE ON col1,
        col2
    )
;

Zipping arrays into rows

ZIP takes one or more plain arrays and combines them into an array of rows, pairing elements by position. If the arrays have different lengths, shorter ones are padded with NULL.

SELECT
    zip_one := ZIP(ARRAY(1, 2, 3) AS my_field),
    zip_many := ZIP(ARRAY(1, 2, 3) AS my_field1, ARRAY('a', 'b') AS my_field2)
;

Merging arrays of rows by position

ARRAY_MERGE without a join clause combines two arrays of rows by position: the first row of each array is merged together, the second with the second, and so on. If the arrays have different lengths, shorter ones are padded with NULL.

SELECT
    array1 := ARRAY(ROW(1, 'A'), ROW(2, 'B'))::ROW(col1 BIGINT, col2 VARCHAR)[],
    array2 := ARRAY(ROW(3, 'C'), ROW(4, 'D'))::ROW(col3 BIGINT, col4 VARCHAR)[],
    merged := ARRAY_MERGE(array1, array2)
;

Merging arrays of rows by key

When two arrays share a field name, ARRAY_MERGE(... VIA field) matches equal values. The first array is the left side: its rows remain when no match exists, with NULL in fields from the other array.

WITH
    key_id := INPUT(BIGINT),
    array1 := ARRAY(ROW(key_id AS field_1, 'A'), ROW(2, 'B'))::ROW(
        num BIGINT,
        letter VARCHAR
    )[],
    array2 := ARRAY(ROW(1, TRUE), ROW(2, FALSE))::ROW(
        num BIGINT,
        num_is_odd BOOLEAN
    )[]
SELECT
    key_id,
    array1,
    array2,
    joined_merge := ARRAY_MERGE(
        array1,
        array2
        VIA num
    )
FOR
    key_id := BIND_VALUES(ARRAY(1, 2, 3))
;

If the right array has duplicate keys, every matching pair is emitted. This fan-out is useful when you intend to reshape or aggregate the result, but it is not an implicit “pick one” operation.

Merging arrays of rows by predicate

Use ARRAY_MERGE(... ON predicate) when the arrays have different key names or need a range, as-of, distance, or other non-equi condition. Field names in the predicate identify the two array sides directly.

WITH
    products := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT) AS product_id,
        ARRAY(52.20, 302.00, 1200.05) AS price
    ),
    bands := ZIP(
        ARRAY(0., 100., 1000.) AS price_min,
        ARRAY(100., 1000., 100000.) AS price_max,
        ARRAY('low', 'mid', 'high') AS band
    ),
    merged := ARRAY_MERGE(
        products,
        bands
        ON PRICE_MIN <= PRICE AND PRICE < PRICE_MAX
    )
SELECT
    merged
;

ON has the same left-array and fan-out semantics as VIA: unmatched left rows remain, and every matching right row produces an output row. To choose one match, follow the merge with TRANSFORM() and an aggregate such as MAX_BY().

Carrying scalars into rows

CARRY broadcasts one or more scalar values into every row of an array, adding them as new fields. This is useful when entity-level attributes (a customer's country, a total revenue figure) need to be available alongside each row for downstream computation inside a TRANSFORM.

SELECT
    orders := ARRAY(ROW(101, 49.99), ROW(102, 129.00), ROW(103, 15.50))::ROW<
        order_id BIGINT,
        price DECIMAL(10,2)
    >[],
    customer_revenue := 500.00,
    country := 'ES',
    enriched := CARRY(customer_revenue, country INTO orders)
;

Each scalar becomes a new field. The field name defaults to the feature name, or you can rename it with AS. This is equivalent to the longer ARRAY_MERGE(ZIP(REPEAT(scalar, ARRAY_COUNT(array)) AS name), array) pattern, but more concise and efficient.

Structural operations (dedicated pages)

The main structural operations on arrays of rows each have their own page:

• Extend enriches each row by joining on an external feature. Use it when you need to add data from another entity (e.g., adding product names to an array of order rows).

• Transform applies a full subquery to the rows — map, filter, aggregate, sort, or limit. Think of it as running a mini FeatureQL query inside each entity.

• Lookup with indexes builds a MAP from an array of rows for repeated key lookups. Use INDEX_UNIQUE() when each key maps to one row, or INDEX_MULTI() when keys can match many rows — then ELEMENT_AT_KEY() / ELEMENTS_AT_KEY() instead of scanning with TRANSFORM(... WHERE ...).

• Recurse starts from a seed ROW and produces an array of rows — one per recursive step. Use it for running totals and sequences (iterative mode), or for walking trees and graphs with VIA … BIND TO … FOLLOW … (traversal mode). The result composes with TRANSFORM() like any other array of rows.