Relational queries are nice tool to have in a vector language. They form a special sub-language operating on tables somewhat similar to SQL. The term "record" referenced below means a row of fields taken from a table.

In order to execute a query, one must first define a query expression, compile it into internal representation and apply "fetch" verb to get results. All three stages are done explicitly.

Overall syntax for query stages looks like:

q: ?[ t; w; g; f; ot; os ]; // define query
cq: . q; // compile query
res:  fe#cq; // fetch query results

whеre

t is a table-like data source. It might be a table/query/join/union expression.

w is a nested list of conditions. It might be an empty list to define no conditions.

g is a grouping dict or 0b expression for none.

f is a field dict.

ot is an optional "take" argument. It might be either a scalar or a two-element integer vector. It defines a range of record indices to limit results.

os is an optional "sorting" argument which must be a nested list of fields to sort with direction.

Let's have a look at a simple example first. It just creates a single table, a "q" query, compiles it into "cq" and fetches all table data into "res".

o)t:(+:)`a`b`c`d!(1 2 3;3 4 5;6 7 8;("111";"222";"333"));
o)q:?[ t; (); 0b; `a`b`c`d!`a`b`c`d ];
o)cq:. q;
o)res:0N#cq;
o)res
a b c d
-----------
1 3 6 "111"
2 4 7 "222"
3 5 8 "333"
o)

As you can see, the verb "fetch" is a dyad expecting either null to fetch all query results in one go, or an amount of records if positive number.

Fields defined in fourth argument are a dictionary whеre keys are expected as a symbol vector. Keys define query field names. Dictionary values define fields as symbols or expressions from data source (first argument). To rename query field, just change dictionary keys as appropriate.

o)t:(+:)`a`b`c`d!(1 2 3;3 4 5;6 7 8;("111";"222";"333"));
o)q:?[ t; (); 0b; `ra`rb`rc`rd!`a`b`c`d ];
o)cq:. q;
o)res:0N#cq;
o)res
ra rb rc rd
--------------
1  3  6  "111"
2  4  7  "222"
3  5  8  "333"
o)

While field references are unique symbols (e.g. from a single source table), underlying query engine is fine with scalar symbols as fields. However, queries might become too complex if different tables have the same field names. To disambiguate between tables, table aliases are used.

Defining table alias is done via providing a two-element list as a data source instead of a single table. The first element is a symbol defining alias symbol, the second one is a table, join or another data source to bind.

To reference a field via alias, use monadic ~ with a two-element symbol vector:

o)t:(+:)`a`b`c`d!(1 2 3;3 4 5;6 7 8;("111";"222";"333"));
o)q:?[ (`alias;t); (); 0b; `ra`rb`rc`rd!((~`alias`a);(~`alias`b);(~`alias`c);(~`alias`d)) ];
o)cq:. q;
o)res:0N#cq;
o)res
ra rb rc rd
--------------
1  3  6  "111"
2  4  7  "222"
3  5  8  "333"
o)

Field definition in queries can also contain various expressions calculated in-flight.

Expressions are defined using functional/"lispy" syntax:

o)t:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)0N#.(?[t;();0b;`d`e!((-:;`a);(+;`b;`c))])
d  e
-----
-1 9
-2 11
-3 13
o)

To identify table rows, use virtual field `i. It's merely an automatically generated zero-based index.

o)t:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)0N#.(?[t;();0b;`a`b`c`i!`a`b`c`i])
a b c i
-------
1 3 6 0
2 4 7 1
3 5 8 2
o)

Constants can be used in place of field, conditions, etc.

o)t:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)0N#.(?[t;();0b;`a`b`c`const!(`a;`b;`c;10)])
a b c const
-----------
1 3 6 10
2 4 7 10
3 5 8 10
o)

To distinguish between symbol constants and fields, symbol constants must be enlisted.

o)t:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)0N#.(?[t;();0b;`a`b`c`const!(`a;`b;`c;,`d)])
a b c const
-----------
1 3 6 d
2 4 7 d
3 5 8 d
o)