Nested ​

Nested stores a collection of named records. Each record has the same fields and declared field types. Dotted field access returns an array of that field's values across the records.

In CoordinatorDB, a Nested value occupies one table column and behaves as an array of named tuples.

Syntax ​

Nested(name1 T1, name2 T2, ...)

Every field must have a unique name. Use Nullable(T) for a field that can contain missing values.

Input values ​

A Nested column accepts either JSON rows or a JSON object of parallel arrays. The parallel arrays must all have the same length. Missing, duplicate or unknown fields, and unequal array lengths, are rejected.

To change records, assign a new typed value to the entire Nested column. Individual nested fields are not separate table columns.

Examples ​

Creating order items ​

CREATE TABLE orders(id UInt32, items Nested(sku String, quantity UInt16));
INSERT INTO orders VALUES
    (1, '{"sku":["book","pen"],"quantity":[1,2]}');

SELECT id, items.sku, items.quantity
FROM orders
FORMAT csv;

1,"[""book"",""pen""]","[1,2]"

Replacing the item collection ​

The row-oriented form supplies one array per named record:

UPDATE orders
SET items = '[["book",3],["pencil",4]]'
WHERE id = 1;

SELECT items.sku, items.quantity
FROM orders
FORMAT csv;

"[""book"",""pencil""]","[3,4]"

Filtering on a projected field ​

SELECT id
FROM orders
WHERE has(items.sku, 'book')
FORMAT csv;

1

See Array and Tuple for the underlying value contracts.