🔍

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.

Queries support is currently limited to "select" polyad & "upsert" triad.

Select

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

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

… where

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.

Compilation & fetching results

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)

Note a space between "dot" monad and "q" variable: cq:. q

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/renaming

Fields defined in fourth argument are a dictionary where 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)

Table aliases

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 expressions

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)

Virtual field "i"

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

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)

next >>> WHERE