Query User Guide

Preliminaries

There are currently two types of query: Select and Proximity. To describe how to use them we must define some terminology. Also, the following example table will be used to illustrate examples:

id	group	text	number	label	truth	date
1	a	This is some text.	42	labelled	false	2016-06-23
2	a	This isn't some text.		unlabelled	true	1970-01-01
3	b	Could be some text.	3.14	labelled		2012-12-21
4	b	Text text text text.	1.61	unlabelled	false	2019-03-29

Literals

Select and Proximity queries utilise the same mechanism for establishing whether conditions on properties on records are true or false, these are called literals. A literal is composed of a column name (or key), a type of condition, and an argument (or arg). To establish whether a records matches the value of the key must confirm to the arg under the type of condition. The following types of condition are available:

Types of condition

type	desc	example arg
present	Whether a value for the key exists for the record.	true or false
equals	Whether the value is exactly equal to the arg.	42 or labelled
regex	Whether the text representation of the value matches regex pattern of the arg.	(?i)\d+(st|nd|rd|th) of (Jan|Feb|Mar|Arp|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) matches dates in the form 2nd of March or 3RD OF APRIL ignoring case.
date	(Requires the field to be of DATE2 type) Whether the value falls within the specified date range.	before 1999-12-31 or 2006-03-01 to 2010-11-04

Each literal is designated a letter which is used to refer to them in expressions. For example, a literal could be:

a -> number : present : true

In our example table this would be true for rows with the id 1, 3, and 4.

Expressions

Expressions are composed of references to literals and Boolean operators. Like literals, they resolve to a truth value for a given record but can express more complex conditions. The logical operators are OR |, AND &, and NOT !. Parentheses ( ) are also allowed to control order of precedence. For example, given our literal a above, and

b -> label : equals : labelled

and

c -> date : date : after 2015-01-01

the expression a&b&c would match the record with id 1 only.

Select

Selects return records which are true for a given expression called the filter. The scope of the filter expression is restricted to individual records. The examples above are essentially employing a Select query. Select can also specify a number of other behaviours which affect how the records are returned. These are Order By, Partition, and Limit. Incidentally, the Select query corresponds directly to the SQL statement of the same name.

Order By

The order in which the results are returned can be specified with a list of fields (keys). Records will be ordered according to values of the first Order By, then subsequent Order By's will be used as tie-breakers. Currently the ordering defaults to ascending. Options to specify descending, and whether to treat string values numerical will be added.

Partition

When a partition field is specified records will be returned in blocks where each block will consist of all records which exhibit same value for the partition field. Each block is exhaustive with regard to the distinct values of the partition field.

Limit

When a limit is specified the Select will return a maximum of that number of records.

Proximity

The Proximity query returns records that satisfy expressions within some proximity to one another Three expressions can specified to compose a Proximity query, two are required and one is optional. The required two are target and proximity, the optional one is table filter. Records which match the target expression which exist within the proximity of records which match the proximity expression, where distance is a positive integer. Results include records in between target and proximity. Finally, the notion of proximity must be specified for this to make sense. Proximity is defined by partitioning and ordering the data, controlled by the partition key and order by query parameters; these exhibit the same semantics as the partition and order by described in the Select above. For example, given:

a -> number : present : true

b -> truth : equals : false

where target is a, proximity is b, proximity is 4, the partition key is group, and the order by is id, records with id 3 and 4 would be returned from the example table. One might assume the entire table would be returned since the records 1 looks to be within the proximity range; however, due to the partition grouping a's and b's together, and that proximity does not transgress partition boundaries, it is not.

The third optional expression table filter is applied to the data before applying the partition and order by thus constraining context in which proximity is defined. This can be useful for selecting from only a subset of the original table, or further defining the notion of proximity by include only specific records.