SQL - Filtering

The Where condition is shared among many SQL commands.

Syntax

[<item>] <operator> <item>

Items

And item can be:

What Description Example Available since
field Document field where price > 1000000 0.9.1
field<indexes> Document field part. To know more about field part look at the full syntax: Document_Field_Part where tags[name=’Hi’] or tags[0-3] IN (‘Hello’) and employees IS NOT NULL 1.0rc5
record attribute Record attribute name with @ as prefix where @class = ‘Profile’ 0.9.21
column The number of the column. Useful in Column Database where column(1) > 300 0.9.1
any() Represents any field of the Document. The condition is true if ANY of the fields matches the condition where any() like ‘L%’ 0.9.10
all() Represents all the fields of the Document. The condition is true if ALL the fields match the condition where all() is null 0.9.10
functions Any function between the defined ones where distance(x, y, 52.20472, 0.14056 ) <= 30 0.9.25
$variable Context variable prefixed with $ where $depth <= 3 1.2.0

Record attributes

Name Description Example Available since
@this returns the record it self select @this.toJSON() from Account 0.9.25
@rid returns the RecordID in the form <cluster:position>. It’s null for embedded records. NOTE: using @rid in where condition slow down queries. Much better to use the RecordID as target. Example: change this: select from Profile where @rid = #10:44 with this: select from #10:44 @rid = #11:0 0.9.21
@class returns Class name only for record of type Schema Aware. It’s null for the others @class = ‘Profile’ 0.9.21
@version returns the record version as integer. Version starts from 0. Can’t be null @version > 0 0.9.21
@size returns the record size in bytes @size > 1024 0.9.21
@fields returns the number of fields in document select @fields from V -
@type returns the record type between: ‘document’, ‘column’, ‘flat’, ‘bytes’ @type = ‘flat’ 0.9.21

Operators

Conditional Operators

Apply to Operator Description Example Available since
any = Equals to name = ‘Luke’ 0.9.1
string like Similar to equals, but allow the wildcard ‘%’ that means ‘any’ name like ‘Luk%’ 0.9.1
any < Less than age < 40 0.9.1
any <= Less than or equal to age <= 40 0.9.1
any > Greater than age > 40 0.9.1
any >= Greater than or equal to age >= 40 0.9.1
any <> Not equals (same of !=) age <> 40 0.9.1
any BETWEEN The value is between a range. It’s equivalent to <field> >= <from-value> AND <field> <= <to-value> price BETWEEN 10 and 30 1.0rc2
any IS Used to test if a value is NULL children is null 0.9.6
record, string (as class name) INSTANCEOF Used to check if the record extends a class @this instanceof ‘Customer’ or @class instanceof ‘Provider’ 1.0rc8
collection IN contains any of the elements listed name in [‘European’,’Asiatic’]
collection CONTAINS true if the collection contains at least one element that satisfy the next condition. 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’) 0.9.7
collection CONTAINSALL true if all the elements of the collection satisfy the next condition children containsAll (name = ‘Luke’) 0.9.7
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’ 0.9.22
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 0.9.22
string CONTAINSTEXT used with 89cd72a14eb5493801e99a43c5034685. Current limitation is that it must be the unique condition of a query. 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’ 0.9.22
string MATCHES Matches the string using a [http://www.regular-expressions.info/ Regular Expression] text matches ‘\b[A-Z0-9.%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}\b’ 0.9.6
any TRAVERSE[(<minDepth> [,<maxDepth> [,<fields>]] This function was born before the SQL Traverse statement and today it’s pretty limited. Look at Traversing graphs to know more about traversing in better ways.
true if traversing the declared field(s) at the level from <minDepth> to <maxDepth> matches the condition. A minDepth = 0 means the root node, maxDepth = -1 means no limit: traverse all the graph recursively. If <minDepth> and <maxDepth> are not used, then (0, -1) will be taken. If <fields> is not passed, than any() will be used.		 select from profile where any() traverse(0,7,’followers,followings’) ( address.city.name = ‘Rome’ ) 0.9.10 and 0.9.24 for <fields> parameter

Logical Operators

Operator Description Example Available since
AND true if both the conditions are true name = ‘Luke’ and surname like ‘Sky%’ 0.9.1
OR true if at least one of the condition is true name = ‘Luke’ or surname like ‘Sky%’ 0.9.1
NOT true if the condition is false. NOT needs parenthesis on the right with the condition to negate not ( name = ‘Luke’) 1.2

Mathematics Operators

Apply to Operator Description Example Available since
Numbers + Plus age + 34 1.0rc7
Numbers - Minus salary - 34 1.0rc7
Numbers * Multiply factor * 1.3 1.0rc7
Numbers / Divide total / 12 1.0rc7
Numbers % Mod total % 3 1.0rc7

Starting from v1.4 OrientDB supports the eval() function to execute complex operations. Example:

  1. select eval( "amount * 120 / 100 - discount" ) as finalPrice from Order

Methods

Also called “Field Operators”, are are treated on a separate page.

Functions

All the SQL functions are treated on a separate page.

Variables

OrientDB 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) Since
$parent Get the parent context from a sub-query. Example: select from V let $type = ( traverse * from $parent.$current.children ) SELECT and TRAVERSE 1.2.0
$current Current record to use in sub-queries to refer from the parent’s variable SELECT and TRAVERSE 1.2.0
$depth The current depth of nesting TRAVERSE 1.1.0
$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 1.1.0
$stack The List of operation in the stack. Use it to access to the history of the traversal TRAVERSE 1.1.0
$history The set of all the records traversed as a Set<ORID> TRAVERSE 1.1.0

To set custom variable use the LET keyword.