Filtering

any

= or ==

Equals. A list on the right side is compared as a whole, so name = ['a','b'] matches nothing, with or without an index; use IN to match any of several values (since v26.10.2; before, an index expanded the list like IN). Two lists, or two maps, are equal when they hold the same elements in the same order (the same keys for a map) and the numbers in them compare as two numbers do, so tags = [1, 2] also matches a list written with 64-bit integers by the Java API or a client (since v26.11.1; before, the elements were compared by Java class and [1, 2] differed from the same list of long values). Every other element is compared as = compares two scalars, so two values = reports equal are equal as elements too: a java.util.Date, an Instant, a Calendar or a java.sql.Timestamp is equal to the stored LocalDateTime of the same instant inside a list or a map, as it is on its own (since v26.11.1; before, the list was a different value and <> answered its row). <>, IN and CONTAINS follow the same rule. A list indexed BY ITEM holds one entry per element, so it is not used for tags = [1, 2]: the records are scanned. A set, an array or any other collection is compared under the same element rule as a list: two sets whatever their order, any other pair in the order they iterate (since v26.11.1; before, only a java.util.List was, so a Set holding a Date differed from the stored list of the same instant)

name = 'Luke'

any

<=>

Null-safe-equals, is also true if left and right operands are NULL

name <=> word

string

LIKE

Similar to equals, but allows the wildcards ‘%’ that means "any characters", none included ('' LIKE '%' is true, since v26.10.2), and ‘?’ that means "any single character". Use \% and \? to match the characters themselves. LIKE is case sensitive. A pattern with a literal start ('Luk%') uses an ordered index on the property, except a case insensitive one; a pattern starting with a wildcard does not.

name LIKE 'Luk%'

string

ILIKE

Similar to LIKE, but ILIKE is case insensitive.

name ILIKE 'lUk%'

string

NOT LIKE

Negation of LIKE, equivalent to NOT (x LIKE y)

name NOT LIKE 'Leia'

string

NOT ILIKE

Negation of ILIKE, equivalent to NOT (x ILIKE y)

name NOT ILIKE 'lEIA'

any

<

Less than

age < 40

any

<=

Less or equal to

age <= 40

any

>

Greater than

age > 40

any

>=

Greater or equal to

age >= 40

any

<> or !=

Not equal to

age <> 40

any

BETWEEN

The value is between a range. It’s equivalent to <field> >= <from-value> AND <field> <= <to-value>

price BETWEEN 10 AND 30

any

IS

Used to test if a value is NULL

children IS NULL

any

IS NOT

Used to test if a value is not NULL

children IS NOT NULL

record, string (as type name)

INSTANCEOF

Used to check if the record extends a type

@this INSTANCEOF 'Customer' OR @type INSTANCEOF 'Provider'

collection

IN

contains any of the elements listed. Uses the index on the property, including the leading columns of a composite index (transactionId IN (...) on an index over (transactionId, date)), with every list form: IN (a, b), IN [a, b] and a bound parameter. The listed values are looked up in index order, so an ORDER BY on the indexed columns needs no extra sort step. The searched value is converted to the type of the listed items when needed, so ? IN doubleList with the String '7' finds 7.0 with or without an index, like CONTAINS. A property on the left side is never converted: stringProp IN [7.0] does not match '7', exactly like stringProp = 7.0 and the index. Without an index, IN (?) with a single parameter and IN (SELECT ...) compare numbers and dates by value too, so a Long 7 matches an Integer 7, and 1.50 matches 1.5 (since v26.10.1)

name IN ['European','Asiatic']

collection

NOT IN

contains all of the elements not listed. Does not use index: an index can look up the listed values, not their complement, so the type is scanned. If the type is large, add a second indexed condition to the query to drive the lookup

name NOT IN ['European','Asiatic']

collection

CONTAINS

true if the collection contains at least one element that satisfy the next condition. Use @this as right item for lists. Condition can be a single item: in this case the behaviour is like the IN operator. A one-element list on the right, CONTAINS [x], compares x under the = rule, so a BINARY value matches by content and an Integer matches a stored Long (since v26.11.1)

children CONTAINS (name = 'Luke') ; map.values() CONTAINS (name = 'Luke') ; ['a','b'] CONTAINS (@this ILIKE 'A')

collection

CONTAINSALL

true if every element of the right collection is contained in the left collection. A single value on the left is treated as a one-element collection (since v26.10.1; before, it was always false). CONTAINSALL (<condition>) is true when every element satisfies any condition, e.g. items CONTAINSALL (name IS NOT NULL) (any condition since v26.10.1; before, only OR conditions). On a list indexed BY ITEM the index looks up one of the elements and every record it returns is checked for the others, so a right side of several elements uses the index too, and answers as a scan does whether it is a list, a set or an array parameter such as int[] (since v26.11.1; before, several elements were scanned, and an array of several elements answered like CONTAINSANY). An empty right side is true for every collection and is answered by a scan. A BINARY (byte[]) right side is one value searched for, as CONTAINS and CONTAINSANY read it (since v26.11.1; before, it was expanded into its bytes)

children.name CONTAINSALL ['Luke','Leia']

collection

CONTAINSANY

true if at least one element of the right collection is contained in the left collection. A single value on the left is treated as a one-element collection, so uuid CONTAINSANY ['u1','u2'] answers like uuid IN ['u1','u2'] (since v26.10.1; before, it was always false). CONTAINSANY (<condition>) is true when at least one element satisfies the condition, so it is false on an empty collection (since v26.10.1; before, it behaved like CONTAINSALL)

children.name CONTAINSANY ['Jacen','Jaina','Ben']

map

CONTAINSKEY

true if the map contains at least one key equals to the requested. You can also use map.keys() CONTAINS in place of it

connections CONTAINSKEY 'Luke'

map

CONTAINSVALUE

true if the map contains at least one value equals to the requested. You can also use map.values() CONTAINS in place of it

connections CONTAINSVALUE 10:3

string

CONTAINSTEXT

When used against an indexed field, a lookup in the index will be performed with the text specified as key. When there is no index a simple Java indexOf will be performed. So the result set could be different if you have an index or not on that field

text CONTAINSTEXT 'jay'

string

MATCHES

Matches the string using a Regular Expression. Use the modifier (?s) to make . match also newlines, and (?i) for case insensitive patterns.

text MATCHES `\b[A-Z0-9.%+-@[A-Z0-9.-]\.[A-Z]{2,4}\b]`

AND

true if both the conditions are true

name = 'Luke' AND surname LIKE 'Sky%'

OR

true if at least one of the condition is true

name = 'Luke' OR surname LIKE 'Sky%'

NOT

true if the condition is false. NOT needs parenthesis on the right with the condition to negate

NOT (name = 'Luke')