Documents

A record's payload is a tree. Objects nest, arrays hold elements, and records in one table need not share a shape — which is the point of holding documents at all.

DEFINE COLLECTION people;
CREATE people = { name: 'ada', address: { city: 'Paris', zip: '75001' }, tags: ['urgent', 'old'] };
CREATE people = { name: 'grace', address: { city: 'Lyon' } };
CREATE people = { name: 'alan', address: 'Paris' };
CREATE people = { name: 'edsger' };

None of those writes names an id, so the collection's counter supplies one per record in the order they were written — people:1 through people:4 — and each write answers with the number it used.

Note people:3, whose address is a string where the others hold an object. Nothing prevents that, and the rules below say exactly what happens when a question reaches it.

Routes

A route names something inside the tree.

SELECT * FROM people WHERE address.city = 'Paris';
SELECT * FROM people WHERE tags[0] = 'urgent';

A position is a position, not a membership test — tags[1] = 'old' asks about the second element and nothing else.

A route that reaches nothing is not an error

Four ways to reach nothing, all of which answer with no rows rather than failing:

SELECT * FROM people WHERE postcode = 'x';              -- no such root
SELECT * FROM people WHERE address.city.first = 'x';    -- through a non-object
SELECT * FROM people WHERE address[0] = 'x';            -- an object by position
SELECT * FROM people WHERE tags[9] = 'old';             -- past the end

This matters more than it looks. In a store where shapes differ by design, a question that failed whenever one record disagreed would be unanswerable in practice.

A position is a whole number, though — tags[-1] is UnexpectedToken, refused where it is written rather than quietly meaning "the last one".

Routes can be indexed

DEFINE INDEX by_home_city ON people FIELDS address.city;
SELECT * FROM people WHERE address.city = 'Paris';

The same record the scan answered. A prefix pattern becomes a range over the same index:

SELECT * FROM people WHERE address.city LIKE 'Par%';

An index on a route is not an index on what the route starts from — WHERE address = 'Paris' still finds people:3, whose address is a string, and does it by scan.

A composite index may mix a route and a plain field:

DEFINE INDEX by_city_and_name ON people FIELDS address.city, name;

Projecting

A read may ask for part of a record:

SELECT name FROM people;
SELECT address.city FROM people;

A projected route answers under the last step of its name — city, above.

A position has no such name, so it needs one:

SELECT address.city AS home, tags[0] AS first_tag FROM people:1;

Without it, SELECT tags[0] FROM people is refused — UnnamedProjection. And two projections answering under one name are refused too:

SELECT address.city, work.city FROM people;

DuplicateProjection. Both would arrive as city and one would win silently.

A projection does not change the access path: SELECT name FROM people WHERE address.city = 'Paris' still uses the index.

[*] — every element, not one of them

A route holding [*] denotes the values it reaches rather than a value. Two contexts have a rule for several.

A comparison holds when any element satisfies it:

SELECT * FROM people WHERE tags[*] = 'urgent';
SELECT * FROM people WHERE tags[*] = 'old';

people:1 both times — its first element answers the first question and its second answers the second, which is the whole of what any means. No element matching is no match rather than a failure. And negating any means none:

SELECT * FROM people WHERE NOT tags[*] = 'urgent';

Three records — including people:4, which has no tags at all.

A projection answers with all of them:

SELECT tags[*] AS all_tags FROM people;

Every record answers, because zero elements is an empty array rather than a missing row.

Where several has no meaning, it is refused

SELECT array::len(tags[*]) AS n FROM people;

Refused — SeveralOutsideAComparison. It stands as the whole projected value or not at all; nested inside a call there is no rule for what the call receives.

SELECT * FROM people ORDER BY tags[*];

Also refused. There is no rule for ordering by several values, and inventing one would sort records by a rule nobody could predict.

Multikey indexes

One entry per element rather than one per record — which is what lets an index answer a question about elements without answering it with a fact about arrays.

DEFINE INDEX by_tag ON people FIELDS tags[*];
SELECT * FROM people WHERE tags[*] = 'old';

The same record the scan answered, and the node now says via index.

Three combinations are refused, each because it has more than one honest reading:

DEFINE INDEX by_tag_unique ON people FIELDS tags[*] UNIQUE;

SeveralInAUniqueIndex — unique across elements, or unique per record?

DEFINE INDEX by_tag_search ON people FIELDS tags[*] SEARCH;

SeveralInAnAnalysedIndex — a search index decides its own multiplicity.

DEFINE INDEX by_two ON people FIELDS tags[*], aliases[*];

SeveralRoutesInOneIndex — the cross product of two arrays is not what anybody means.