SQL - Filtering

The Where condition is shared among many SQL commands.

Syntax

[<item>] <operator> <item>

Items

And item can be:

WhatDescriptionExampleAvailable since
fieldDocument fieldwhere price > 10000000.9.1
field<indexes>Document field part. To know more about field part look at the full syntax: propertieswhere tags[name='Hi'] or tags[0-3] IN ('Hello') and employees IS NOT NULL1.0rc5
record attributeRecord attribute name with @ as prefixwhere @class = 'Profile'0.9.21
columnThe number of the column. Useful in Column Databasewhere column(1) > 3000.9.1
any()Represents any field of the Document. The condition is true if ANY of the fields matches the conditionwhere any() like 'L%'0.9.10
all()Represents all the fields of the Document. The condition is true if ALL the fields match the conditionwhere all() is null0.9.10
functionsAny function between the defined oneswhere distance(x, y, 52.20472, 0.14056 ) <= 300.9.25
$variableContext variable prefixed with $where $depth <= 31.2.0

Record attributes

NameDescriptionExampleAvailable since
@thisreturns the record it selfselect @this.toJSON() from Account0.9.25
@ridreturns the Record ID 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 Record ID as target. Example: change this: select from Profile where @rid = #10:44 with this: select from #10:44 *@rid = #11:00.9.21
@classreturns Class name only for record of type Schema Aware. It's null for the others@class = 'Profile'0.9.21
@versionreturns the record version as integer. Version starts from 0. Can't be null@version > 00.9.21
@sizereturns the record size in bytes@size > 10240.9.21
@fieldsreturns the number of fields in documentselect @fields from V-
@typereturns the record type between: 'document', 'column', 'flat', 'bytes'@type = 'flat'0.9.21

Operators

Conditional Operators

Apply toOperatorDescriptionExampleAvailable since
any=Equals toname = 'Luke'0.9.1
stringlikeSimilar to equals, but allow the wildcard '%' that means 'any'name like 'Luk%'0.9.1
any<Less thanage < 400.9.1
any<=Less than or equal toage <= 400.9.1
any>Greater thanage > 400.9.1
any>=Greater than or equal toage >= 400.9.1
any<>Not equals (same of !=)age <> 400.9.1
anyBETWEENThe value is between a range. It's equivalent to <field> >= <from-value> AND <field> <= <to-value>price BETWEEN 10 and 301.0rc2
anyISUsed to test if a value is NULLchildren is null0.9.6
record, string (as class name)INSTANCEOFUsed to check if the record extends a class@this instanceof 'Customer' or @class instanceof 'Provider'1.0rc8
collectionINcontains any of the elements listedname in ['European','Asiatic']
collectionCONTAINStrue 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 operatorchildren contains (name = 'Luke') - map.values() contains (name = 'Luke')0.9.7
collectionCONTAINSALLtrue if all the elements of the collection satisfy the next conditionchildren containsAll (name = 'Luke')0.9.7
collectionCONTAINSANYtrue if any the elements of the collection satisfy the next conditionchildren containsAny (name = 'Luke')
mapCONTAINSKEYtrue if the map contains at least one key equals to the requested. You can also use map.keys() CONTAINS in place of itconnections containsKey 'Luke'0.9.22
mapCONTAINSVALUEtrue if the map contains at least one value equals to the requested. You can also use map.values() CONTAINS in place of itconnections containsValue 10:30.9.22
stringCONTAINSTEXTWhen 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 fieldtext containsText 'jay'0.9.22
stringMATCHESMatches 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
anyTRAVERSE[(<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

OperatorDescriptionExampleAvailable since
ANDtrue if both the conditions are truename = 'Luke' and surname like 'Sky%'0.9.1
ORtrue if at least one of the condition is truename = 'Luke' or surname like 'Sky%'0.9.1
NOTtrue if the condition is false. NOT needs parenthesis on the right with the condition to negatenot ( name = 'Luke')1.2

Mathematics Operators

Apply toOperatorDescriptionExampleAvailable since
Numbers+Plusage + 341.0rc7
Numbers-Minussalary - 341.0rc7
Numbers*Multiplyfactor * 1.31.0rc7
Numbers/Dividetotal / 121.0rc7
Numbers%Modtotal % 31.0rc7

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

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:

NameDescriptionCommand(s)Since
$parentGet the parent context from a sub-query. Example: select from V let $type = ( traverse * from $parent.$current.children )SELECT and TRAVERSE1.2.0
$currentCurrent record to use in sub-queries to refer from the parent's variableSELECT and TRAVERSE1.2.0
$depthThe current depth of nestingTRAVERSE1.1.0
$pathThe 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)TRAVERSE1.1.0
$stackThe List of operation in the stack. Use it to access to the history of the traversalTRAVERSE1.1.0
$historyThe set of all the records traversed as a Set<ORID>TRAVERSE1.1.0

To set custom variable use the LET keyword.