Shaping the answer

A read decides two things: which records answer, and what each one looks like. Conditions and reads is the first. This is the second.

DEFINE TABLE people (name string, age int, tags array, home object, secret string);
CREATE people:1 = { name: 'ada', age: 17, tags: ['a', 'b'], home: { city: 'Paris', zip: '75' }, secret: 'x' };
CREATE people = { name: 'grace', age: 45, tags: ['c'], home: { city: 'Lyon', zip: '69' }, secret: 'y' };

The star composes

SELECT * answers with the record whole. It is not a mode the read is in — it is one item in the projection list, and other items sit beside it:

SELECT *, age + 1 AS next FROM people;

1: { age: 17, home: { city: 'Paris', zip: '75' }, name: 'ada', next: 18, secret: 'x', tags: ['a', 'b'] }
2: { age: 45, home: { city: 'Lyon', zip: '69' }, name: 'grace', next: 46, secret: 'y', tags: ['c'] }

Everything the record holds, plus the one thing it does not. Adding a computed field is the common case and it does not cost you the rest of the record.

OMIT subtracts from the star

SELECT * OMIT secret FROM people;

1: { age: 17, home: { city: 'Paris', zip: '75' }, name: 'ada', tags: ['a', 'b'] }
2: { age: 45, home: { city: 'Lyon', zip: '69' }, name: 'grace', tags: ['c'] }

It reaches into a route, not just a top-level name:

SELECT * OMIT home.zip FROM people;

1: { age: 17, home: { city: 'Paris' }, name: 'ada', secret: 'x', tags: ['a', 'b'] }
2: { age: 45, home: { city: 'Lyon' }, name: 'grace', secret: 'y', tags: ['c'] }

OMIT is written beside the projection rather than among the clauses after FROM, because it says what the star contributes rather than what the read does.

Naming a route that no record has is not an error — the answer simply has nothing to remove. That is the right behaviour for a schemaless store, where "this field is absent" is a fact about a record and not about the table. It is also the reason OMIT is the wrong tool for keeping a secret out of an answer: it removes what is there, and it cannot tell you it removed nothing. Choose the fields you do want when the point is that something must not appear.

A name comes from the route's last step

SELECT home.city FROM people;

1: { city: 'Paris' }
2: { city: 'Lyon' }

AS overrides it:

SELECT name AS who FROM people;

Two projections that would answer under the same name are refused rather than resolved:

SELECT name, name AS name FROM people;

Refused — DuplicateProjection.

error: two projections answer under the name "name" (at 21..25); one of them needs `AS`

Silently keeping the last one is how a field a client depends on gets replaced by a field that happens to be spelled the same.

A position is un-nameable, so index into a route and give the result a name:

SELECT tags[0] AS first FROM people;

1: { first: 'a' }
2: { first: 'c' }

ONLY is about how many records answer

It sits between FROM and the source, because that is what it qualifies:

SELECT * FROM ONLY people:1;

1: { age: 17, home: { city: 'Paris', zip: '75' }, name: 'ada', secret: 'x', tags: ['a', 'b'] }

The answer is still a list of records, and it travels carrying a flag that says ONLY was asked — so a client library can hand you a single value rather than a one-element collection, while the wire format stays one shape. What the word does on the server is refuse the case the caller was not expecting:

SELECT * FROM ONLY people;

Refused — NotAlone.

error: `ONLY` says one record answers this read and 2 did; narrow it, or drop
the word and take the list

Read it as at most one. A read that matches nothing is not a failed assertion:

SELECT * FROM ONLY people:99;

(0 record(s), via record)

Those are two different questions and it is worth being clear about which one ONLY answers. "I expect a single record" is a statement about the shape of the answer and it is what ONLY checks. "I expect this record to exist" is a statement about the data, and an empty answer is the ordinary way to learn it is false — so it is reported, not raised.

SPLIT ON changes how many records there are

SELECT name, tags FROM people SPLIT ON tags;

1: { name: 'ada', tags: 'a' }
1: { name: 'ada', tags: 'b' }
2: { name: 'grace', tags: 'c' }

One record holding a list of two becomes two records holding one each, everything else repeated. The record id repeats too, which is the honest reporting: these are two views of one record and not two records.

SPLIT is applied before anything that counts — before GROUP BY, before ORDER BY, before LIMIT:

SELECT name, tags FROM people SPLIT ON tags LIMIT 2;

1: { name: 'ada', tags: 'a' }
1: { name: 'ada', tags: 'b' }

Two of the split records, both from one original, rather than the first two people. The clause order in the grammar is the order of application, deliberately, so that reading a statement top to bottom tells you what happens in what sequence.