3.2. Operators¶

3.2.1. Arithmetic¶

Addition:

a + b

Subtraction:

a - b

Multiplication:

a * b

Division:

a / b

Modulo (remainder):

a % b

Power:

a ^ b

a and b must be null or number.

3.2.2. Equality¶

Equality:

a = b

For inequality:

a != b
a <> b

a and b must be the same type, and accepted types are:

null
number
string
array
object

In the case of an array both arrays must be the same length and contain exactly the same element in the same order:

SELECT [1, 2] = [1, 2]

true

SELECT [1, 2] = [1, "2"]

false

SELECT [1, 2] = [2, 1]

false

In the case of an object both objects must contain the same amount of keys and each key must exist in both objects with the exact same value.

SELECT {"foo": 123} = {"foo": 123}

true

SELECT {"foo": 123} = {"foo": 123, "bar": null}

false

Inequality has all the same rules but in reverse.

3.2.3. Greater or Less Than¶

Greater than:

a > b

Greater than or equal to:

a >= b

Less than:

a < b

Less than or equal to:

a <= b

a and b must be the same type, and accepted types are:

null
number
string

When comparing strings it follows the same rules as how Lua compares strings.

3.2.4. Concatenation¶

Concatenation:

a || b

Will concatenate the string representations of both sides. For example 3 || 5 is 35. Special values will be converted as follows:

Value	String Representation
`null`	`""`
`true`	`"true"`
`false`	`"false"`

You cannot concatenate arrays or objects on either or both sides.

3.2.5. Logical¶

For all logical operations a and b are only allowed to be null or boolean.

Logical AND:

a AND b

Results:

AND	`true`	`false`
`true`	`true`	`false`
`false`	`false`	`false`

Logical OR:

a OR b

Results:

OR	`true`	`false`
`true`	`true`	`true`
`false`	`true`	`false`

3.2.6. Regular Expressions¶

Regular Expressions:

value LIKE regex
value NOT LIKE regex

value must be a string, but can be of any length.

regex uses the SQL rules for LIKE expressions.

Character	Description
`.`	Match any single character.
`%`	Match zero or more characters.

3.2.6.1. Examples¶

Test if a string starts with another string:

SELECT "Bob Smith" LIKE "Bob %"

Test if a string ends with another string:

SELECT "Bob Smith" LIKE "% Smith"

3.2.7. Checking Types¶

The following can be used to test the types of a value:

value IS null
value IS true
value IS false
value IS boolean
value IS number
value IS string
value IS array
value IS object

Each of the combinations can be used with NOT like:

value IS NOT boolean

The case of the type (boolean) is not important and there is no specific convention on case.

3.2.8. Set Membership¶

To test the existence of a value in a set:

a IN (b1, b2, ...)
a NOT IN (b1, b2, ...)

Will return true if a exists in one of the b values. There must be at least one b value. Comparison of each element follows the same rules as the = operator.

If a is null or any of the b values are null then the result is null. This is to conform is the SQL standard in dealing with null values.

3.2.9. Containment¶

To test if a value sits between two other values (inclusive):

a BETWEEN b AND c
a NOT BETWEEN b AND c

Is exactly equivalent to:

a >= b AND a <= c
a < b OR a > c

If at least one of a, b or c is null then the result will always be null.

3.2.10. Operator Precedence¶

Operator/Element	Associativity	Description
`-`	right	unary minus
`^`	left	exponentiation
`*` `/` `%`	left	multiplication, division, modulo
`+` `-`	left	addition, subtraction
`IS`		test for `true`, `false`, `null`
`IN`		set membership
`BETWEEN`		containment
`LIKE` `ILIKE`		string pattern matching
`<` `>`		less than, greater than
`=`	right	equality, assignment
`NOT`	right	logical negation
`AND`	left	logical conjunction
`OR`	left	logical disjunction