Project

A query gives rows, and ordering arranges them. project shapes each row into the document that the caller needs. The caller receives that document without a second assembly step.

The examples start from Ada, Bob and Cy. They also add Grace as Ada's manager. A reference points to an existing entity, so create Grace first:

TRON

51 B

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	4C 6E616D65	txt "name"
09	5C 4772616365	txt "Grace"
0F	0F 0A 04000000 0900 0000	map leaf, size 10: @4 → @9
19	7C 235F6772616365	txt "#_grace"
21	0F 0A 19000000 0F00 0000	map leaf, size 10: @19 → @F
2B	21000000 00000000	root → @21; previous: none

The first commit returns Grace's ID, 1, in this fresh database. The next patch creates Ada, Bob and Cy with IDs 3, 4 and 5. Ada's manager references Grace:

TRON

418 B

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	5C 236C696E6B	txt "#link"
00A	02 01000000000000 00	i64 1
013	0F 0A 04000000 0A00 0000	map leaf, size 10: @4 → @A
01D	7C 6D616E61676572	txt "manager"
025	0F 0A 1D000000 1300 0000	map leaf, size 10: @1D → @13
02F	3C 616765	txt "age"
033	02 24000000000000 00	i64 36
03C	0F 0A 2F000000 3300 0000	map leaf, size 10: @2F → @33
046	4C 726F6C65	txt "role"
04B	8C 656E67696E6565 72	txt "engineer"
054	0F 0A 46000000 4B00 0000	map leaf, size 10: @46 → @4B
05E	4C 6E616D65	txt "name"
063	3C 416461	txt "Ada"
067	0F 0A 5E000000 6300 0000	map leaf, size 10: @5E → @63
071	07 16 20910000 2500 0000 3C000000 5400 0000 67000000	map branch, size 22, depth 0 (root), slots 5, 8, 12, 15: @25 → @3C → @54 → @67
087	5C 236C696E6B	txt "#link"
08D	5C 235F616461	txt "#_ada"
093	0F 0A 87000000 8D00 0000	map leaf, size 10: @87 → @8D
09D	3C 616765	txt "age"
0A1	02 29000000000000 00	i64 41
0AA	0F 0A 9D000000 A100 0000	map leaf, size 10: @9D → @A1
0B4	6C 6D656E746F72	txt "mentor"
0BB	0F 0A B4000000 9300 0000	map leaf, size 10: @B4 → @93
0C5	4C 726F6C65	txt "role"
0CA	8C 656E67696E6565 72	txt "engineer"
0D3	0F 0A C5000000 CA00 0000	map leaf, size 10: @C5 → @CA
0DD	4C 6E616D65	txt "name"
0E2	3C 426F62	txt "Bob"
0E6	0F 0A DD000000 E200 0000	map leaf, size 10: @DD → @E2
0F0	07 16 00990000 AA00 0000 BB000000 D300 0000 E6000000	map branch, size 22, depth 0 (root), slots 8, 11, 12, 15: @AA → @BB → @D3 → @E6
106	3C 616765	txt "age"
10A	02 1D000000000000 00	i64 29
113	0F 0A 06010000 0A01 0000	map leaf, size 10: @106 → @10A
11D	4C 726F6C65	txt "role"
122	8C 64657369676E65 72	txt "designer"
12B	0F 0A 1D010000 2201 0000	map leaf, size 10: @11D → @122
135	4C 6E616D65	txt "name"
13A	2C 4379	txt "Cy"
13D	0F 0A 35010000 3A01 0000	map leaf, size 10: @135 → @13A
147	07 12 00910000 1301 0000 2B010000 3D01 0000	map branch, size 18, depth 0 (root), slots 8, 12, 15: @113 → @12B → @13D
159	4C 235F6379	txt "#_cy"
15E	0F 0A 59010000 4701 0000	map leaf, size 10: @159 → @147
168	5C 235F626F62	txt "#_bob"
16E	0F 0A 68010000 F000 0000	map leaf, size 10: @168 → @F0
178	5C 235F616461	txt "#_ada"
17E	0F 0A 78010000 7100 0000	map leaf, size 10: @178 → @71
188	07 12 80210000 5E01 0000 6E010000 7E01 0000	map branch, size 18, depth 0 (root), slots 7, 8, 13: @15E → @16E → @17E
19A	88010000 00000000	root → @188; previous: none

Root and fields

project has root and fields members. root is an expression that gives the entity to read for each row. fields is the shape of the output, and it cannot be empty.

TRON

401 B

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	2C 3F65	txt "?e"
007	0E 0D 00 0100 010000 00 04000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @4
014	2C 3F65	txt "?e"
017	4C 6E616D65	txt "name"
01C	3C 416461	txt "Ada"
020	0E 15 00 0700 030000 00 14000000 170000 00 1C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @14 → @17 → @1C
035	2C 3F65	txt "?e"
038	3C 616765	txt "age"
03C	2C 3F76	txt "?v"
03F	0E 15 00 0700 030000 00 35000000 380000 00 3C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @35 → @38 → @3C
054	0E 11 00 0300 020000 00 20000000 3F0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @20 → @3F
065	1C 2A	txt "*"
067	2C 3F76	txt "?v"
06A	02 02000000000000 00	i64 2
073	0E 15 00 0700 030000 00 65000000 670000 00 6A000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @65 → @67 → @6A
088	7C 646F75626C6564	txt "doubled"
090	0F 0A 88000000 7300 0000	map leaf, size 10: @88 → @73
09A	7C 6D697373696E67	txt "missing"
0A2	8C 2E756E6B6E6F77 6E	txt ".unknown"
0AB	0F 0A 9A000000 A200 0000	map leaf, size 10: @9A → @A2
0B5	3C 77686F	txt "who"
0B9	5C 2E6E616D65	txt ".name"
0BF	0F 0A B5000000 B900 0000	map leaf, size 10: @B5 → @B9
0C9	07 0E 06000000 AB00 0000 BF000000	map branch, size 14, depth 1, slots 1, 2: @AB → @BF
0D7	4C 696E666F	txt "info"
0DC	0F 0A D7000000 9000 0000	map leaf, size 10: @D7 → @90
0E6	4C 626F7373	txt "boss"
0EB	DC 2E6D616E616765 722E6E616D65	txt ".manager.name"
0F9	0F 0A E6000000 EB00 0000	map leaf, size 10: @E6 → @EB
103	07 12 C0200000 C900 0000 DC000000 F900 0000	map branch, size 18, depth 0 (root), slots 6, 7, 13: @C9 → @DC → @F9
115	4C 726F6F74	txt "root"
11A	2C 3F65	txt "?e"
11D	0F 0A 15010000 1A01 0000	map leaf, size 10: @115 → @11A
127	6C 6669656C6473	txt "fields"
12E	0F 0A 27010000 0301 0000	map leaf, size 10: @127 → @103
138	07 0E 0A000000 1D01 0000 2E010000	map branch, size 14, depth 0 (root), slots 1, 3: @11D → @12E
146	5C 7768657265	txt "where"
14C	0F 0A 46010000 5400 0000	map leaf, size 10: @146 → @54
156	4C 66696E64	txt "find"
15B	0F 0A 56010000 0700 0000	map leaf, size 10: @156 → @7
165	7C 70726F6A656374	txt "project"
16D	0F 0A 65010000 3801 0000	map leaf, size 10: @165 → @138
177	07 12 0A040000 4C01 0000 5B010000 6D01 0000	map branch, size 18, depth 0 (root), slots 1, 3, 10: @14C → @15B → @16D
189	77010000 00000000	root → @177; previous: none

The query selects Ada and gives one row:

TRON

157 B

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	7C 646F75626C6564	txt "doubled"
0C	02 48000000000000 00	i64 72
15	0F 0A 04000000 0C00 0000	map leaf, size 10: @4 → @C
1F	7C 6D697373696E67	txt "missing"
27	00	nil
28	0F 0A 1F000000 2700 0000	map leaf, size 10: @1F → @27
32	3C 77686F	txt "who"
36	3C 416461	txt "Ada"
3A	0F 0A 32000000 3600 0000	map leaf, size 10: @32 → @36
44	07 0E 06000000 2800 0000 3A000000	map branch, size 14, depth 1, slots 1, 2: @28 → @3A
52	4C 696E666F	txt "info"
57	0F 0A 52000000 1500 0000	map leaf, size 10: @52 → @15
61	4C 626F7373	txt "boss"
66	5C 4772616365	txt "Grace"
6C	0F 0A 61000000 6600 0000	map leaf, size 10: @61 → @66
76	07 12 C0200000 4400 0000 57000000 6C00 0000	map branch, size 18, depth 0 (root), slots 6, 7, 13: @44 → @57 → @6C
88	0E 0D 00 0100 010000 00 76000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @76
95	88000000 00000000	root → @88; previous: none

Field kinds

The value determines the field kind.

Value	Kind	Result
Text that starts with `.`	Path	Reads that field of the root entity
An object	Fields	A nested shape with its own fields
Anything else	Expression	An expression for each row

A path follows entity references, so .manager.name reads the name of the entity that manager refers to. A path that reaches nothing gives null instead of an error, as .unknown does above.

A nested object gives a nested document. An expression reads the row's variables, so info.doubled doubles ?v from the where clause.

Building text

An expression field builds text with format. The template comes first, and each {} accepts the next argument.

TRON

324 B

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	2C 3F65	txt "?e"
007	0E 0D 00 0100 010000 00 04000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @4
014	2C 3F65	txt "?e"
017	4C 6E616D65	txt "name"
01C	3C 416461	txt "Ada"
020	0E 15 00 0700 030000 00 14000000 170000 00 1C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @14 → @17 → @1C
035	2C 3F65	txt "?e"
038	3C 616765	txt "age"
03C	2C 3F76	txt "?v"
03F	0E 15 00 0700 030000 00 35000000 380000 00 3C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @35 → @38 → @3C
054	2C 3F65	txt "?e"
057	4C 6E616D65	txt "name"
05C	2C 3F6E	txt "?n"
05F	0E 15 00 0700 030000 00 54000000 570000 00 5C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @54 → @57 → @5C
074	0E 15 00 0700 030000 00 20000000 3F0000 00 5F000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @20 → @3F → @5F
089	6C 666F726D6174	txt "format"
090	8C 7B7D206973207B 7D	txt "{} is {}"
099	2C 3F6E	txt "?n"
09C	2C 3F76	txt "?v"
09F	0E 19 00 0F00 040000 00 89000000 900000 00 99000000 9C0000 00	array leaf, size 25, shift 0, bitmap 000F, count 4 (root): @89 → @90 → @99 → @9C
0B8	5C 6C6162656C	txt "label"
0BE	0F 0A B8000000 9F00 0000	map leaf, size 10: @B8 → @9F
0C8	4C 726F6F74	txt "root"
0CD	2C 3F65	txt "?e"
0D0	0F 0A C8000000 CD00 0000	map leaf, size 10: @C8 → @CD
0DA	6C 6669656C6473	txt "fields"
0E1	0F 0A DA000000 BE00 0000	map leaf, size 10: @DA → @BE
0EB	07 0E 0A000000 D000 0000 E1000000	map branch, size 14, depth 0 (root), slots 1, 3: @D0 → @E1
0F9	5C 7768657265	txt "where"
0FF	0F 0A F9000000 7400 0000	map leaf, size 10: @F9 → @74
109	4C 66696E64	txt "find"
10E	0F 0A 09010000 0700 0000	map leaf, size 10: @109 → @7
118	7C 70726F6A656374	txt "project"
120	0F 0A 18010000 EB00 0000	map leaf, size 10: @118 → @EB
12A	07 12 0A040000 FF00 0000 0E010000 2001 0000	map branch, size 18, depth 0 (root), slots 1, 3, 10: @FF → @10E → @120
13C	2A010000 00000000	root → @12A; previous: none

TRON

51 B

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	5C 6C6162656C	txt "label"
0A	9C 41646120697320 3336	txt "Ada is 36"
14	0F 0A 04000000 0A00 0000	map leaf, size 10: @4 → @A
1E	0E 0D 00 0100 010000 00 14000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @14
2B	1E000000 00000000	root → @1E; previous: none

An argument is an expression, never a path. A path reads a field only as the whole value of a field, so .name inside format gives the text .name. The query above binds ?n in where, which is how a field of the root entity reaches a template.

Names and paths

A field name cannot be empty, and it cannot start with $. Each segment of a path must be a name, so .. and a trailing . are errors. A segment cannot use the reserved array storage prefix stardust/list/.

TRON

100 B

Offset (hex)	Bytes (hex)	Meaning
00	54 52 4F 4E	magic TRON
04	3C 77686F	txt "who"
08	6C 2E6E616D652E	txt ".name."
0F	0F 0A 04000000 0800 0000	map leaf, size 10: @4 → @8
19	4C 726F6F74	txt "root"
1E	2C 3F65	txt "?e"
21	0F 0A 19000000 1E00 0000	map leaf, size 10: @19 → @1E
2B	6C 6669656C6473	txt "fields"
32	0F 0A 2B000000 0F00 0000	map leaf, size 10: @2B → @F
3C	07 0E 0A000000 2100 0000 32000000	map branch, size 14, depth 0 (root), slots 1, 3: @21 → @32
4A	7C 70726F6A656374	txt "project"
52	0F 0A 4A000000 3C00 0000	map leaf, size 10: @4A → @3C
5C	52000000 00000000	root → @52; previous: none

Error

Invalid_Project

The path .name. ends with an empty segment. .name reads that field, and .manager.name follows the reference in manager to read a field of another entity.

Result mode

project selects positional rows when the query does not have resultMode. You can also set resultMode positional.

resultMode object and project give two shapes for one row. A query that contains both gives an error.

TRON

277 B

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	2C 3F65	txt "?e"
007	0E 0D 00 0100 010000 00 04000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @4
014	2C 3F65	txt "?e"
017	4C 6E616D65	txt "name"
01C	3C 416461	txt "Ada"
020	0E 15 00 0700 030000 00 14000000 170000 00 1C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @14 → @17 → @1C
035	2C 3F65	txt "?e"
038	3C 616765	txt "age"
03C	2C 3F76	txt "?v"
03F	0E 15 00 0700 030000 00 35000000 380000 00 3C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @35 → @38 → @3C
054	0E 11 00 0300 020000 00 20000000 3F0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @20 → @3F
065	3C 77686F	txt "who"
069	5C 2E6E616D65	txt ".name"
06F	0F 0A 65000000 6900 0000	map leaf, size 10: @65 → @69
079	4C 726F6F74	txt "root"
07E	2C 3F65	txt "?e"
081	0F 0A 79000000 7E00 0000	map leaf, size 10: @79 → @7E
08B	6C 6669656C6473	txt "fields"
092	0F 0A 8B000000 6F00 0000	map leaf, size 10: @8B → @6F
09C	07 0E 0A000000 8100 0000 92000000	map branch, size 14, depth 0 (root), slots 1, 3: @81 → @92
0AA	5C 7768657265	txt "where"
0B0	0F 0A AA000000 5400 0000	map leaf, size 10: @AA → @54
0BA	4C 66696E64	txt "find"
0BF	0F 0A BA000000 0700 0000	map leaf, size 10: @BA → @7
0C9	AC 726573756C744D 6F6465	txt "resultMode"
0D4	6C 6F626A656374	txt "object"
0DB	0F 0A C9000000 D400 0000	map leaf, size 10: @C9 → @D4
0E5	7C 70726F6A656374	txt "project"
0ED	0F 0A E5000000 9C00 0000	map leaf, size 10: @E5 → @9C
0F7	07 16 0A060000 B000 0000 BF000000 DB00 0000 ED000000	map branch, size 22, depth 0 (root), slots 1, 3, 9, 10: @B0 → @BF → @DB → @ED
10D	F7000000 00000000	root → @F7; previous: none

Error

Project_Result_Mode_Conflict

Project and page limits

A projected shape is not a page boundary, so project and page together are an error.

TRON

336 B

Offset (hex)	Bytes (hex)	Meaning
000	54 52 4F 4E	magic TRON
004	2C 3F65	txt "?e"
007	0E 0D 00 0100 010000 00 04000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @4
014	2C 3F65	txt "?e"
017	4C 6E616D65	txt "name"
01C	3C 416461	txt "Ada"
020	0E 15 00 0700 030000 00 14000000 170000 00 1C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @14 → @17 → @1C
035	2C 3F65	txt "?e"
038	3C 616765	txt "age"
03C	2C 3F76	txt "?v"
03F	0E 15 00 0700 030000 00 35000000 380000 00 3C000000	array leaf, size 21, shift 0, bitmap 0007, count 3 (root): @35 → @38 → @3C
054	0E 11 00 0300 020000 00 20000000 3F0000 00	array leaf, size 17, shift 0, bitmap 0003, count 2 (root): @20 → @3F
065	2C 3F76	txt "?v"
068	0E 0D 00 0100 010000 00 65000000	array leaf, size 13, shift 0, bitmap 0001, count 1 (root): @65
075	4C 73697A65	txt "size"
07A	02 01000000000000 00	i64 1
083	0F 0A 75000000 7A00 0000	map leaf, size 10: @75 → @7A
08D	3C 77686F	txt "who"
091	5C 2E6E616D65	txt ".name"
097	0F 0A 8D000000 9100 0000	map leaf, size 10: @8D → @91
0A1	4C 726F6F74	txt "root"
0A6	2C 3F65	txt "?e"
0A9	0F 0A A1000000 A600 0000	map leaf, size 10: @A1 → @A6
0B3	6C 6669656C6473	txt "fields"
0BA	0F 0A B3000000 9700 0000	map leaf, size 10: @B3 → @97
0C4	07 0E 0A000000 A900 0000 BA000000	map branch, size 14, depth 0 (root), slots 1, 3: @A9 → @BA
0D2	5C 7768657265	txt "where"
0D8	0F 0A D2000000 5400 0000	map leaf, size 10: @D2 → @54
0E2	4C 66696E64	txt "find"
0E7	0F 0A E2000000 0700 0000	map leaf, size 10: @E2 → @7
0F1	7C 6F726465724279	txt "orderBy"
0F9	0F 0A F1000000 6800 0000	map leaf, size 10: @F1 → @68
103	4C 70616765	txt "page"
108	0F 0A 03010000 8300 0000	map leaf, size 10: @103 → @83
112	07 0E 00280000 F900 0000 08010000	map branch, size 14, depth 1, slots 11, 13: @F9 → @108
120	7C 70726F6A656374	txt "project"
128	0F 0A 20010000 C400 0000	map leaf, size 10: @120 → @C4
132	07 16 2A040000 D800 0000 E7000000 1201 0000 28010000	map branch, size 22, depth 0 (root), slots 1, 3, 5, 10: @D8 → @E7 → @112 → @128
148	32010000 00000000	root → @132; previous: none

Error

Invalid_Page

A projection reads the current document of each entity, so project with a temporal read is also an error.

Run it

database query · query run.