OpO FAST Web Server and Database
TQL
OpO

The WHERE clause can be used in all operations. It is used to provide an expression that narrows the scope of the operation. For example in a SELECT operation providing an expression that indicates the address.state must equal CA narrows the scope to those JSON documents with a state of California.

Some functions only support numeric type which include integer, float, and time.

The WHERE clause value can be either an expression or a reference number. A reference number value must be a hexidecimal number that matches a reference number returned by a previous operation. An expression must be one of the functions described on this page. Note that FILTER expressions can use some additional functions.

BETWEEN(path, minimum, maximum, [min_incl], [max_incl])

Matches numeric nodes between a minimum and maximum value. By default the matches are inclusive of the minimum and maximum. The optional arguments allow the inclusivity to be changed to exclusive separately for the minimum and maximum. By default the min_incl and max_incl are true. If set to false the corresponding limit becomes exclusive. An error is returned if used with non-numeric minimum or maximum.

Friendly Example

WHERE BETWEEN(age, 21, 80)

JSON Example

{ "where": ["between", "age", 21, 80] }
EQ(path, value)

Matches a node that has the same value as provided at the end of the path provided. Any type is acceptable.

Friendly Example

WHERE EQ(age, 63)

JSON Example

{ "where": ["eq", "age", 63] }
GT(path, value)

Matches a node that has a value greater than the provided value. If a integer or float is provided then both integer and floats are checked. If the value provided is a time then only time nodes are checked. Any other type results in an error.

Friendly Example

WHERE GT(age, 63)

JSON Example

{ "where": ["gt", "age", 63] }
GTE(path, value)

Matches a node that has a value greater than or equals to the provided value. If a integer or float is provided then both integer and floats are checked. If the value provided is a time then only time nodes are checked. Any other type results in an error.

Friendly Example

WHERE GTE(age, 63)

JSON Example

{ "where": ["gte", "age", 63] }
IN(path, value, ...)

Match any node that has a value equal to one of the values provided in the argument list after the path. This can be used with any type.

Friendly Example

WHERE IN(age, 40, 50, 60, 70)

JSON Example

{ "where": ["in", "age", 40, 50, 60, 70] }
LT(path, value)

Matches a node that has a value less than the provided value. If a integer or float is provided then both integer and floats are checked. If the value provided is a time then only time nodes are checked. Any other type results in an error.

Friendly Example

WHERE LT(age, 63)

JSON Example

{ "where": ["lt", "age", 63] }
LTE(path, value)

Matches a node that has a value less than or equal to the provided value. If a integer or float is provided then both integer and floats are checked. If the value provided is a time then only time nodes are checked. Any other type results in an error.

Friendly Example

WHERE LTE(age, 63)

JSON Example

{ "where": ["lte", "age", 63] }