Filtering

The WHERE condition is shared among many SQL commands. A condition results in a boolean; and a compound condition, consisting of multiple conditions that are combined with logical operators, is resolved from left to right.

Syntax

[<item>] <operator> <item>

Items

And item can be:

What Description Example

field

Document field

WHERE price > 1000000

field<indexes>

Document field part. To know more about field part look at the full syntax: Document-API-Property

WHERE tags[name='Hi'] OR tags[0...3] IN ('Hello') AND employees IS NOT NULL

record attribute

Record attribute name with @ as prefix

WHERE @type = 'Profile'

Functions

Any Function between the defined ones

WHERE distance(x, y, 52.20, 0.14) <= 30

$variable

Context variable prefixed with $

WHERE $depth <= 3

Record attributes

Name Description Example

@this

returns the record itself

SELECT @this.toJSON() FROM Account

@rid

returns the RID in the form bucket:position. It is null for embedded records. NOTE: using @rid in a "Where" condition slows down queries. Much better to use the RID as target. Example: change SELECT FROM Profile WHERE @rid = #10:44 to SELECT FROM #10:44

@rid = #11:0

@size

returns the record size in bytes

@size > 1024

@type

returns the record type between: 'document', 'column', 'flat', 'bytes'

@type = 'flat'

Operators

Conditional Operators

Apply to Operator Description Example

any

= or ==

Equals

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" and ‘?’ that means "any single character". LIKE is case sensitive. Does not use index!

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

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

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

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

collection

CONTAINSANY

true if at least one element of the right collection is contained in the left collection

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]`
LIKE, ILIKE, MATCHES are not using full-text indexes.
CONTAINS asks whether the collection on the left holds a single value: either one matching the condition, or, when the right side is a plain value, one equal to it. Put a collection on the right and it is searched for as one element, so tags CONTAINS ['red','blue'] matches a row whose tags holds the nested list ['red','blue'], and not a row whose tags is ['red','green','blue']. To compare two collections, use CONTAINSALL (every element of the right is present on the left) or CONTAINSANY (at least one is). This applies equally when the right side is produced by an expression such as 'red blue'.split(' ').

Logical Operators

Operator Description Example

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')

Mathematics Operators

Apply to Operator Description Example

Numbers

+

Plus

age + 34

Numbers

-

Minus

salary - 34

Numbers

*

Multiply

factor * 1.3

Numbers

/

Divide

total / 12

Numbers

%

Mod

total % 3

Methods

Also called "Field Operators", are SQL-Methods.

Variables

ArcadeDB supports variables managed in the context of the command/query. By default, some variables are created. Below the table with the available variables:

Name Description Command(s)

$parent

Get the parent context from a sub-query. Example: SELECT FROM V LET $type = (TRAVERSE * FROM $parent.$current.children)

SELECT and TRAVERSE

$current

Current record to use in sub-queries to refer from the parent’s variable

SELECT and TRAVERSE

$depth

The current depth of nesting

TRAVERSE

$path

The string representation of the current path. Example: #6:0.in.#5:0#.out. You can also display it with SELECT $path FROM (TRAVERSE * FROM V)

TRAVERSE

$stack

The List of operation in the stack. Use it to access to the history of the traversal

TRAVERSE

$history

The set of all the records traversed as a Set<RID>

TRAVERSE

To set custom variable use the LET keyword.

Wildcards

Symbol Description Example

%

Matches all strings that contain an unknown substring of any length at the position of %

"%DB" "A%DB" "Arcade%" all match "ArcadeDB"

?

Matches all strings that contain an unknown character at the position of ?

"N?SQL" matches "NoSQL" but not "NewSQL"

Filtering for strings containing wildcards characters can be done by escaping with backslash, i.e. \%, \?.