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 join

When two arrays share a common field, ARRAY_MERGE ... JOINED ON matches rows by that field's value — like a SQL JOIN, but within the entity. Rows without a match get NULL for the missing fields.

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,
    ARRAY_MERGE(ARRAY1, ARRAY2 JOIN ON num) as JOINED_MERGE
FOR
    KEY_ID := BIND_VALUES(ARRAY[1,2,3])
;

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.