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.

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 first 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])
;

Extending and transforming

The two most powerful operations on arrays of rows have their own pages:

• 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.