Expression Syntax

XINA expressions translate to MySQL expressions, which are evaluated as a query is executed.

All expressions have a standard form as a JSON object, with a type property specifying the expression type, and additional properties as needed by that expression type.

Additionally, certain expression types may be represented using a short form, which is formatted as a JSON object with a single property prefixed with the $ character.

Literals

Literal expressions represent a single, discrete value.

Null

The MySQL NULL value. May also be specified with the JSON null value.

Property	Value
`type`	`"null"`

Example (as object)

{ "type": "null" }

Example (as JSON literal)

null

Number

A numeric literal value. The value may be provided as a native JSON number, or encoded as a string. May also be provided directly as a JSON number value.

Property	Value
`type`	`"number"`
`value`	`number` or `string`

Example (as object)

{
 "type"  : "number",
 "value" : 123
}

Example (as JSON literal)

String

A string literal value. May also be provided directly as a JSON string.

Property	Value
`type`	`"string"`
`value`	`string`

Example (as object)

{
 "type"  : "string",
 "value" : "foo"
}

Example (as JSON literal)

"foo"

Datetime

A datetime literal value. Interpreted by the database as Unix time in milliseconds.

Property	Value
`type`	`"datetime"` or `"dt"`
`value`	`integer` or `string`

If the value provided is an integer it must be the number of milliseconds since the Unix epoch. If the value is a string it must be encoded according to the following syntax, taken from the ISO8601 standard:

date-opt-time     = date-element ['T' [time-element] [offset]]
date-element      = std-date-element | ord-date-element | week-date-element
std-date-element  = yyyy ['-' MM ['-' dd]]
ord-date-element  = yyyy ['-' DDD]
week-date-element = xxxx '-W' ww ['-' e]
time-element      = HH [minute-element] | [fraction]
minute-element    = ':' mm [second-element] | [fraction]
second-element    = ':' ss [fraction]
fraction          = ('.' | ',') digit [digit] [digit]
offset            = 'Z' | (('+' | '-') HH [':' mm [':' ss [('.' | ',') SSS]]])

If the offset is not provided the timezone will be assumed to be UTC.

Supports shorthand syntax with the $dt property.

Property	Value
`$dt`	`integer` or `string`

Local Datetime

A local datetime literal value.

Property	Value
`type`	`"localdatetime"` or `"ldt"`
`value`	`string`

The value must be encoded according to the same syntax as the datetime literal, except with the offset omitted.

Supports shorthand syntax with the $ldt property.

Property	Value
`$ldt`	`string`

Local Date

A local date literal value.

Property	Value
`type`	`"localdate"` or `"ld"`
`value`	`string`

The value must be encoded according to the same syntax as the date-element in the datetime literal.

Supports shorthand syntax with the $ld property.

Property	Value
`$ld`	`string`

Local Time

A local time literal value.

Property	Value
`type`	`"localtime"` or `"lt"`
`value`	`string`

The value must be encoded according to the same syntax as the time-element in the datetime literal.

Supports shorthand syntax with the $lt property.

Property	Value
`$lt`	`string`

Columns

Column expressions specify a column of a table. Although each column type has a separate full syntax, there is a shorthand syntax with the $col property, which infers the column type based on the content.

Property	Value
`$col`	`string` column

column          = system-column | database-column
system-column   = system-table-name '.' system-parameter-name
database-column = database-path ['@' database-table-name] '.' (parameter-name | attribute-name | field-name | blob-attribute)
blob-attribute  = blob-name ':' blob-attribute-name

Examples

user.email : email parameter of the user system table

a.b.record_id : record_id attribute of the record table of database b in group a

a.b@trash.record_id : record_id attribute of the trash table of database b in group a

a.b.c : c field of the record table of database b in group a

System Parameter Column

Specifies a column of a system table.

Property	Value
`type`	`"column"`
`table`	`string`
`column`	`string`

Database Parameter Column

Specifies a parameter column of a database table.

Property	Value
`type`	`"column"`
`database`	database specifier
`table`	`string` table name
`column`	`string` parameter name

Database Attribute Column

Specifies an attribute column of a database table.

Property	Value
`type`	`"column"`
`database`	database specifier
`table`	`string` table name
`column`	`string` attribute name

Database Field Column

Specifies a field column of a database table.

Property	Value
`type`	`"column"`
`database`	database specifier
`table`	`string` table name
`column`	field specifier

Alias

Although the alias is not technically a column, it can refer directly by name to any column in the source, or to an alias of a result column.

Property	Value
`type`	`"alias"`
`value`	`string`

Supports shorthand syntax with the $alias property.

Property	Value
`$alias`	`string`

Evaluations

Evaluations are expressions evaluated by MySQL.

Between

Returns true if the expression e is between min and max.

Property	Value
`type`	`"between"`
`e`	expression
`min`	expression
`max`	expression

Supports shorthand syntax with the $between property. Takes a JSON array of exactly 3 expressions, in the order e, min, and max.

Property	Value
`$between`	array of three expressions

Binary

Binary operation, evaluated as e1 op e2.

Property	Value
`type`	`"binary"`
`op`	`string`
`e1`	expression
`e2`	expression

Valid binary operators are as follows:

Operator	Description
`and`	logical AND
`or`	logical OR
`=`	equal
`!=`	not equal
`>`	greater
`>=`	greater or equal
`<`	less
`<=`	less or equal
`is`	test against `NULL`
`like`	simple pattern matching, see here
`regexp`	advanced pattern matching, see here
`+`	addition
`-`	subtraction
`*`	multiplication
`/`	division
`%`	modulus
`&`	bit-wise AND
`⏐`	bit-wise OR
`<<`	left shift
`>>`	right shift

Supports shorthand syntax with any operator by prefixing it with $. Takes a JSON array of two or more expressions. If more than two expressions are provided, behavior depends on the operator type. Logic and math operators perform each binary operation in order of expressions. For example:

{"$and": [true, true, false]} = (true and true) and false = false

{"$/": [12, 3, 2, 2]} = ((12 / 3) / 2) / 2 = 1

Comparison operators perform a logical AND of the comparisons of the first expression to each additional expression.

{"$=": [0, 1, 2]} = (0 = 1) and (0 = 2) = false

Case

Case logic expression. If the base is provided, returns the then expression of the first case in which when = base. Otherwise returns the first case in which when is true. If no case is satisfied returns else if it is provided, or NULL otherwise.

Property	Value
`type`	`"case"`
`base`	expression (optional)
`cases`	`array` of case options
`else`	expression (optional)

Case Option

Property	Value
`when`	expression
`then`	expression

Collate

Performs the MySQL COLLATE function.

Property	Value
`type`	`"collate"`
`e`	expression
`collation`	`string`

Count Rows

Performs the MySQL COUNT(*) function.

Property	Value
`type`	`"count_rows"`

Example

{ "type": "count_rows" }

Exists

Returns true if the enclosed SELECT returns at least one row.

Property	Value
`type`	`"exists"`
`select`	select

Supports shorthand syntax with the $exists property.

Property	Value
`$exists`	select

Function

Performs a MySQL function. The number of arguments varies depending on the function.

Property	Value
`type`	`"function"`
`function`	`string`
`args`	array of expressions

Available functions are:

Name	Args	Aggregate	Description
`AVG`	1	yes	arithmetic average
`AVG_DISTINCT`	1	yes	arithmetic average of distinct values of argument
`BIT_AND`	1	yes	bit-wise AND
`BIT_OR`	1	yes	bit-wise OR
`BIT_XOR`	1	yes	bit-wise XOR
`CEIL`	1	yes	returns the smallest integer value not less than the argument
`COUNT`	1	yes	returns the number of rows in the which the argument is not `NULL`
`COUNT_DISTINCT`	`n`	yes	returns the number of distinct value(s) of the arguments
`FLOOR`	1	yes	returns the largest integer value not greater than the argument
`MAX`	1	yes	returns the maximum value of the argument
`MIN`	1	yes	returns the minimum value of the argument
`POW`	2	no
`STDDEV_POP`	1	yes	returns the population standard deviation of the argument
`STDDEV_SAMP`	1	yes	returns the sample standard deviation of the argument
`SUM`	1	yes	returns the sum of the argument
`SUM_DISTINCT`	1	yes	returns the sum of the distinct values of the argument
`TRUNCATE`	2	no
`VAR_POP`	1	yes	returns the population variance of the argument
`VAR_SAMP`	1	yes	returns the sample variance of the argument

Supports shorthand syntax by prefixing any function name with $$. For example, { "$$pow": [2, 3] } evaluates to 8.

In

Returns true if an expression is contained in a set of values. If an empty array is provided for values, will always return false.

Property	Value
`type`	`"in"`
`e`	expression
`values`	array of expressions

Supports shorthand syntax with the $in property. Takes an array of a single expression (e), followed by either an array of expression(s) (values) or a SELECT object.

Property	Value
`$in`	array of one expression, then either an array of expressions or a select

In Select

Returns true if e is in the result of the select query.

Property	Value
`type`	`"in_select"`
`e`	expression
`select`	select

Supports shorthand syntax with the $in property (see above).

Select Expression

Returns the value of the first column in the first row of the result set of the query.

Property	Value
`type`	`"select"`
`select`	select

Supports shorthand syntax with the $select property.

Property	Value
`$select`	select

Unary Expression

Unary operator expression, evaluated as op e.

Property	Value
`type`	`"unary"`
`op`	`string`
`e`	expression

Valid unary operators are:

Operator	Description
`not`	logical NOT
`-`	negate
`~`	bit invert

Supports shorthand syntax with any operator by prefixing it with $. Takes a single expression as a value.

Property	Value
`$` `op`	expression