From
Iteration, aliasing, and lateral cross-product behavior of the from clause.
Scanning An
Scanning an empty table yields no rows.
SQL
create table T;
select * from T;
Result
[]
Insertion Order
A table scan returns all rows in insertion order.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Omitting As
Omitting as uses the table name as the alias.
SQL
create table T;
insert into T ({x: 42});
select T.x from T;
Result
[
42
]
An Explicit
An explicit as alias names the binding.
SQL
create table T;
insert into T ({x: 10});
select t.x from T as t;
Result
[
10
]
Row Unwrapped
Select * over one source emits the row unwrapped.
SQL
create table T;
insert into T ({x: 1, y: 2});
select * from T as t;
Result
[
{ "x": 1, "y": 2 }
]
Its Alias
Select . wraps the binding under its alias.
SQL
create table T;
insert into T ({x: 1});
select . from T as t;
Result
[
{ "t": { "x": 1 } }
]
Two Comma
Two comma sources form a Cartesian product, merged by select *.
SQL
create table T;
create table S;
insert into S ({b: 10}, {b: 20});
insert into T ({a: 1}, {a: 2});
select * from T as t, S as s;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 },
{ "a": 2, "b": 10 },
{ "a": 2, "b": 20 }
]
From Cross Projection
A projection list may reference both cross-joined bindings.
SQL
create table T;
create table S;
insert into S ({b: 10}, {b: 20});
insert into T ({a: 1}, {a: 2});
select t.a as a, s.b as b from T as t, S as s;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 },
{ "a": 2, "b": 10 },
{ "a": 2, "b": 20 }
]
Dot Envelope
Select . over two sources keys each binding by its alias.
SQL
create table T;
create table S;
insert into S ({b: 10});
insert into T ({a: 1});
select . from T as t, S as s;
Result
[
{ "t": { "a": 1 }, "s": { "b": 10 } }
]
Both Bindings
A where predicate filters the product across both bindings.
SQL
create table T;
create table S;
insert into S ({b: 10}, {b: 20});
insert into T ({a: 1}, {a: 2});
select t.a as a, s.b as b from T as t, S as s where t.a = 1;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 }
]
An Empty
An empty inner source makes the whole product empty.
SQL
create table T;
create table S;
insert into T ({a: 1}, {a: 2});
select * from T as t, S as s;
Result
[]
Referencing An
Referencing an undeclared table is a static error.
SQL
create table T;
select * from Ghost;
Expected error: static
Referencing An
Referencing an alias not in scope is a static error.
SQL
create table T;
select x.foo from T;
Expected error: static
Earlier Binding
A later source may unnest a collection path on an earlier binding.
SQL
create table T;
insert into T ({items: [1, 2, 3]});
select t.items as items, item as item from T as t, t.items as item;
Result
[
{ "items": [ 1, 2, 3 ], "item": 1 },
{ "items": [ 1, 2, 3 ], "item": 2 },
{ "items": [ 1, 2, 3 ], "item": 3 }
]
Star Scalar
Select * keeps a non-object (scalar) lateral binding under its alias.
SQL
create table T;
insert into T ({items: [1, 2, 3]});
select * from T as t, t.items as item;
Result
[
{ "items": [ 1, 2, 3 ], "item": 1 },
{ "items": [ 1, 2, 3 ], "item": 2 },
{ "items": [ 1, 2, 3 ], "item": 3 }
]
The Unnested
The unnested element binds under its alias in the envelope.
SQL
create table T;
insert into T ({items: [1, 2]});
select . from T as t, t.items as item;
Result
[
{ "t": { "items": [ 1, 2 ] }, "item": 1 },
{ "t": { "items": [ 1, 2 ] }, "item": 2 }
]
Unnest Flattens
Unnest flattens across every outer row in order.
SQL
create table T;
insert into T ({k: 1, items: [10, 11]}, {k: 2, items: [20]});
select t.k as k, item as v from T as t, t.items as item;
Result
[
{ "k": 1, "v": 10 },
{ "k": 1, "v": 11 },
{ "k": 2, "v": 20 }
]
An Empty
An empty collection contributes no rows for that outer binding.
SQL
create table T;
insert into T ({items: []});
select item as v from T as t, t.items as item;
Result
[]
Missing Path
A missing path is treated as empty (inner-join-like).
SQL
create table T;
insert into T ({x: 1});
select item as v from T as t, t.items as item;
Result
[]
Non Array
A non-array source value contributes no rows.
SQL
create table T;
insert into T ({items: 5});
select item as v from T as t, t.items as item;
Result
[]
An Array
An array literal may serve as a From source, scanned element-wise.
SQL
create table T;
select x from [1, 2, 3] as x;
Result
[
1,
2,
3
]
Self Reference
A lateral source may not reference its own alias.
SQL
create table T;
select * from T as t, item.x as item;
Expected error: static
Requires Alias
A lateral collection source requires an alias.
SQL
create table T;
select * from T as t, t.items;
Expected error: static
An Empty
An empty array literal source yields no rows.
SQL
create table T;
select x from [] as x;
Result
[]
Its Alias
Select . wraps each scanned element under its alias.
SQL
create table T;
select . from [1, 2] as x;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Into Them
A value source iterates object elements and may path into them.
SQL
create table T;
select x.a as a from [{a: 1}, {a: 2}] as x;
Result
[
{ "a": 1 },
{ "a": 2 }
]
Non Array
A non-array value source contributes no rows.
SQL
create table T;
select x from 5 as x;
Result
[]
Table Row
A value source re-iterates for every outer table row.
SQL
create table T;
insert into T ({a: 1}, {a: 2});
select . from T as t, [10, 20] as n;
Result
[
{ "t": { "a": 1 }, "n": 10 },
{ "t": { "a": 1 }, "n": 20 },
{ "t": { "a": 2 }, "n": 10 },
{ "t": { "a": 2 }, "n": 20 }
]
Unnest An
Unnest an array of objects and path into each element.
SQL
create table T;
insert into T ({s: [{x: 1}, {x: 2}]}, {s: [{x: 3}]});
select s.x as x from T as t, t.s as s;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]