Skip to main content

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)

123

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