Primary Keys
Declaring key columns and fetching rows by key.
Keys
Whole Objects
A keyless table stores and returns whole objects.
SQL
create table t;
insert into t ({"x": 1, "y": 2, "z": 3});
select * from t;
Result
[
{ "x": 1, "y": 2, "z": 3 }
]
Ones X
A keyless table accepts any object, including ones with no x.
SQL
create table t;
insert into t ({"y": 2, "z": 3});
select * from t;
Result
[
{ "y": 2, "z": 3 }
]
Surrogate Ids
Surrogate ids increment, so rows come back in insertion order.
SQL
create table t;
insert into t ({"x": 3}, {"x": 1}, {"x": 2});
select * from t;
Result
[
{ "x": 3 },
{ "x": 1 },
{ "x": 2 }
]
Int Key
Int key with payload round-trips whole object.
SQL
create table t (x int);
insert into t ({"x": 1, "z": 9});
select * from t;
Result
[
{ "x": 1, "z": 9 }
]
Rows Inserted
Rows inserted out of order come back sorted by the int key.
SQL
create table t (x int);
insert into t ({"x": 3}, {"x": 1}, {"x": 2});
select * from t;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Negative Ints
Negative ints sort before zero and positives (sign-flip encoding).
SQL
create table t (x int);
insert into t ({"x": 1}, {"x": -5}, {"x": 0}, {"x": -1});
select * from t;
Result
[
{ "x": -5 },
{ "x": -1 },
{ "x": 0 },
{ "x": 1 }
]
Re-inserting The
Re-inserting the same key overwrites (last write wins).
SQL
create table t (x int);
insert into t ({"x": 1, "v": 100});
insert into t ({"x": 1, "v": 200});
select * from t;
Result
[
{ "x": 1, "v": 200 }
]
Inserting Without
Inserting without the key field is a schema error.
SQL
create table t (x int);
insert into t ({"z": 9});
Expected error: schema
Wrong Type
A string where an int key is declared is a schema error.
SQL
create table t (x int);
insert into t ({"x": "a"});
Expected error: schema
Int Key
A float subscript coerces like cast(
SQL
create table t (x int);
insert into t ({"x": 1, "z": 9});
select t[1.5];
Result
[
{ "x": 1, "z": 9 }
]
String Key
String key with payload round-trips whole object.
SQL
create table t (x string);
insert into t ({"x": "a", "z": 9});
select * from t;
Result
[
{ "x": "a", "z": 9 }
]
Rows Come
Rows come back in lexicographic key order.
SQL
create table t (x string);
insert into t ({"x": "c"}, {"x": "a"}, {"x": "b"});
select * from t;
Result
[
{ "x": "a" },
{ "x": "b" },
{ "x": "c" }
]
Inserting Without
Inserting without the key field is a schema error.
SQL
create table t (x string);
insert into t ({"z": 9});
Expected error: schema
Wrong Type
A number where a string key is declared is a schema error.
SQL
create table t (x string);
insert into t ({"x": 1});
Expected error: schema
Composite (int,
Composite (int, string) key round-trips whole object.
SQL
create table t (a int, b string);
insert into t ({"a": 1, "b": "x", "z": 9});
select * from t;
Result
[
{ "a": 1, "b": "x", "z": 9 }
]
Sort By
Sort by first component, tie-break on the second.
SQL
create table t (a int, b string);
insert into t ({"a": 2, "b": "a"}, {"a": 1, "b": "y"}, {"a": 1, "b": "x"});
select * from t;
Result
[
{ "a": 1, "b": "x" },
{ "a": 1, "b": "y" },
{ "a": 2, "b": "a" }
]
Missing The
Missing the first key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({"b": "x"});
Expected error: schema
Missing The
Missing the second key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({"a": 1});
Expected error: schema
Wrong Type
Wrong type for the first key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({"a": "q", "b": "x"});
Expected error: schema
Wrong Type
Wrong type for the second key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({"a": 1, "b": 2});
Expected error: schema
Composite (string,
Composite (string, int) key round-trips whole object.
SQL
create table t (a string, b int);
insert into t ({"a": "x", "b": 1, "z": 9});
select * from t;
Result
[
{ "a": "x", "b": 1, "z": 9 }
]
Sort By
Sort by string first, tie-break on the int.
SQL
create table t (a string, b int);
insert into t ({"a": "b", "b": 1}, {"a": "a", "b": 2}, {"a": "a", "b": 1});
select * from t;
Result
[
{ "a": "a", "b": 1 },
{ "a": "a", "b": 2 },
{ "a": "b", "b": 1 }
]
Missing The
Missing the int component is a schema error.
SQL
create table t (a string, b int);
insert into t ({"a": "x"});
Expected error: schema
Type Second
A string where the int component is declared is a schema error.
SQL
create table t (a string, b int);
insert into t ({"a": "x", "b": "y"});
Expected error: schema
Composite (int,
Composite (int, int) key round-trips whole object.
SQL
create table t (a int, b int);
insert into t ({"a": 1, "b": 2, "z": 9});
select * from t;
Result
[
{ "a": 1, "b": 2, "z": 9 }
]
Sort By
Sort by first int, tie-break on the second int.
SQL
create table t (a int, b int);
insert into t ({"a": 2, "b": 1}, {"a": 1, "b": 2}, {"a": 1, "b": 1});
select * from t;
Result
[
{ "a": 1, "b": 1 },
{ "a": 1, "b": 2 },
{ "a": 2, "b": 1 }
]
Missing A
Missing a key component is a schema error.
SQL
create table t (a int, b int);
insert into t ({"a": 1});
Expected error: schema
Composite (string,
Composite (string, string) key round-trips whole object.
SQL
create table t (a string, b string);
insert into t ({"a": "x", "b": "y", "z": 9});
select * from t;
Result
[
{ "a": "x", "b": "y", "z": 9 }
]
Sort By
Sort by first string, tie-break on the second.
SQL
create table t (a string, b string);
insert into t ({"a": "b", "b": "a"}, {"a": "a", "b": "b"}, {"a": "a", "b": "a"});
select * from t;
Result
[
{ "a": "a", "b": "a" },
{ "a": "a", "b": "b" },
{ "a": "b", "b": "a" }
]
Before Ab
A shorter first component sorts before a longer one that shares its prefix, regardless of the second component — proves the string terminator. ("a","z") must sort before ("ab","a").
SQL
create table t (a string, b string);
insert into t ({"a": "ab", "b": "a"}, {"a": "a", "b": "z"});
select * from t;
Result
[
{ "a": "a", "b": "z" },
{ "a": "ab", "b": "a" }
]
Missing A
Missing a key component is a schema error.
SQL
create table t (a string, b string);
insert into t ({"a": "x"});
Expected error: schema
Float Key
A float key column is rejected at create.
SQL
create table t (x float);
Expected error: static
Bool Key
A bool key column is rejected at create.
SQL
create table t (x bool);
Expected error: static
Keyed lookup
Indexing By
Indexing by an existing int key returns the whole row.
SQL
create table t (id int);
insert into t ({"id": 1, "v": "a"});
select t[1];
Result
[
{ "id": 1, "v": "a" }
]
Nil Row
A missing key yields null (dict-get → nil), one row.
SQL
create table t (id int);
insert into t ({"id": 1, "v": "a"});
select t[99];
Result
[
null
]
Indexing An
Indexing an empty table yields null.
SQL
create table t (id int);
select t[1];
Result
[
null
]
Get String Key
A string-keyed table indexes by a string literal.
SQL
create table t (id int);
create table s (id string);
insert into s ({"id": "x", "v": 9});
select s["x"];
Result
[
{ "id": "x", "v": 9 }
]
Re-inserting A
Re-inserting a key overwrites; the lookup sees the latest value.
SQL
create table t (id int);
insert into t ({"id": 1, "v": 100});
insert into t ({"id": 1, "v": 200});
select t[1];
Result
[
{ "id": 1, "v": 200 }
]
The Looked-up
The looked-up row is a value and can be indexed further.
SQL
create table t (id int);
insert into t ({"id": 1, "v": "a"});
select t[1].v;
Result
[
"a"
]
The Lookup
The lookup picks exactly one row out of many, ignoring the rest.
SQL
create table t (id int);
insert into t ({"id": 1, "v": "a"}, {"id": 2, "v": "b"}, {"id": 3, "v": "c"});
select t[2];
Result
[
{ "id": 2, "v": "b" }
]
Get Wrong Type
A string key against an int-keyed table is a schema error.
SQL
create table t (id int);
select t["a"];
Expected error: schema
Get Keyless
A keyless table cannot be indexed by key.
SQL
create table t (id int);
create table k;
select k[1];
Expected error: static
Get Composite Arity
A key tuple longer than the key-column count is a static error.
SQL
create table t (id int);
create table c (a int, b int);
select c[1, 2, 3];
Expected error: static
Matching Row
A full composite key returns the one matching row.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 7, "v": "hit"});
select c["x", 7];
Result
[
{ "a": "x", "b": 7, "v": "hit" }
]
Key Order
A leading-prefix key returns the matching rows as an array in key order.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 2, "v": "q"}, {"a": "x", "b": 1, "v": "p"}, {"a": "y", "b": 9, "v": "r"});
select c["x"];
Result
[
[ { "a": "x", "b": 1, "v": "p" }, { "a": "x", "b": 2, "v": "q" } ]
]
Empty Array
A partial key matching no rows yields an empty array.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 1, "v": "p"});
select c["z"];
Result
[
[]
]
Matching Prefix
A two-column prefix of a three-column key scans only the matching prefix.
SQL
create table t (id int);
create table k (a string, b int, c int);
insert into k ({"a": "x", "b": 7, "c": 2, "v": "n"}, {"a": "x", "b": 7, "c": 1, "v": "m"}, {"a": "x", "b": 8, "c": 9, "v": "o"});
select k["x", 7];
Result
[
[ { "a": "x", "b": 7, "c": 1, "v": "m" }, { "a": "x", "b": 7, "c": 2, "v": "n" } ]
]
The Sub-sequence
The sub-sequence array composes — index into it.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 1, "v": "p"}, {"a": "x", "b": 2, "v": "q"});
select c["x"][0];
Result
[
{ "a": "x", "b": 1, "v": "p" }
]
The Sub-sequence
The sub-sequence array composes — scan it as a value source.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 1, "v": "p"}, {"a": "x", "b": 2, "v": "q"}, {"a": "y", "b": 9, "v": "r"});
select r from c["x"] as r;
Result
[
{ "a": "x", "b": 1, "v": "p" },
{ "a": "x", "b": 2, "v": "q" }
]
Empty Stream
A partial-key source with no match yields zero rows (empty stream, not []).
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 1, "v": "p"});
select r from c["z"] as r;
Result
[]
Source Where
A partial-key source composes with a where filter over the stream.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 1, "v": "p"}, {"a": "x", "b": 2, "v": "q"}, {"a": "x", "b": 3, "v": "s"});
select r from c["x"] as r where r.b > 1;
Result
[
{ "a": "x", "b": 2, "v": "q" },
{ "a": "x", "b": 3, "v": "s" }
]
Source Limit
A partial-key source composes with limit (streamed early termination).
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 1, "v": "p"}, {"a": "x", "b": 2, "v": "q"}, {"a": "x", "b": 3, "v": "s"});
select r from c["x"] as r limit 1;
Result
[
{ "a": "x", "b": 1, "v": "p" }
]
3col Prefix
A two-column prefix of a three-column key streams only the matching prefix.
SQL
create table t (id int);
create table k (a string, b int, c int);
insert into k ({"a": "x", "b": 7, "c": 2, "v": "n"}, {"a": "x", "b": 7, "c": 1, "v": "m"}, {"a": "x", "b": 8, "c": 9, "v": "o"});
select r from k["x", 7] as r;
Result
[
{ "a": "x", "b": 7, "c": 1, "v": "m" },
{ "a": "x", "b": 7, "c": 2, "v": "n" }
]
The Streamed
The streamed sub-sequence matches the element order of the array form, skipping non-prefix rows.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 2, "v": "q"}, {"a": "x", "b": 1, "v": "p"}, {"a": "y", "b": 9, "v": "r"});
select r from c["x"] as r;
Result
[
{ "a": "x", "b": 1, "v": "p" },
{ "a": "x", "b": 2, "v": "q" }
]
Get Composite Miss
A full composite key with no matching row yields null.
SQL
create table t (id int);
create table c (a string, b int);
insert into c ({"a": "x", "b": 7});
select c["x", 8];
Result
[
null
]
Indexing A
Indexing a name that is neither a binding nor a table is unbound.
SQL
create table t (id int);
select ghost[1];
Expected error: static