SQL Where

SQL Where conditions

<wiki:toc max_depth="4" />

Introduction

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>](<item>])	Document field part. To know more about field part look at the full syntax: Document_Field_Part	where **tags[= 'Hi' or *tags[0-3](1]*)** 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 function](name='Linus']}}}*)	Any [function](#Functions) between the defined	where distance(x, y, 52.20472, 0.14056 ) <= 30	0.9.25
[$variable](#Variables)	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
@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	values **in** `[
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	text *containsText* 'jay'	0.9.22
string	MATCHES	Matches the string using a [http://www.regular-expressions.info/ Regular Expression](3,4,5]`	0.9.6
text **matches** '\b[
any	{{{TRAVERSE[( [, [,]](A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b'	0.9.22
*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** name = 'Luke'	Not supported yet

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

Field Operators

These operators apply directly to the fields. They are beyond the SQL standard but you can use it in your queries. You can concatenate N operators in sequence. Note: operators are case-insensitive.

Apply to	Operator	Description	Example	Available since
document, map, list, array	[an item from a multi-value object like a map, a list, an array or a document. For document and maps the item must be a string, for lists and arrays the index as number	select from Profile where contacts[phone](<string>]	Get).left(3) = '+39'	1.0rc5
string	.append('<string>')	Appends a string to another one	select name.**append(' ')**.**append(surname)** from Profile	1.0rc1
string, short, int, long	.asBoolean()	Transforms the field into a Boolean type. If the origin type is a string, then "true" and "false" is checked. If it's a number then 1 means TRUE while 0 means FALSE	online.**toBoolean()** = true	0.9.15
string, long	.asDate()	Transforms the field into a Date type	time.**toDate()** -> time is stored as long type measuring milliseconds since a particular day	0.9.14
string, long	.asDateTime()	Transforms the field into a Date type but parsing also the time information	time.**toDateTime()** < '01-01-2010 00:00:00' -> time is stored as long type measuring milliseconds since a particular day. Returns all the records where time is before the year 2010	0.9.14
any	.asFloat()	Transforms the field into a float type	ray.**toFloat()** > 3.14	0.9.14
any	.asInteger()	Transforms the field into an integer type	value.left(3).**toInteger()** -> Converts the first 3 chars of 'value' field in an integer	0.9.14
any	.asString()	Transforms the field into a string type	salary.**toString()**.indexof('.') > -1 -> True if the salary has decimal	0.9.14
string	.charAt(<pos>)	returns the character of the original string contained in the position 'pos'. 'pos' starts from 0 to string length -1	name.**charAt( 0 )** = 'L'	0.9.7
any	.format('<frmt>')	returns the value formatted using the common "printf" syntax. For the complete reference goto [Java Formatter JavaDoc](http://java.sun.com/j2se/1.5.0/docs/api/java/util/Formatter.html#syntax). (Available since v0.9.5)	salary.**format("%04d")** -> formats a number with 4 digits filling with 0	0.9.8
string	.indexOf('<string>' [<begin> ](,))	returns the position of the <string> inside the item. It returns -1 if no occurrences are found	name.**indexOf( 'street' )** > -1	0.9.10
maps	.keys()	Returns the map's keys as a separate set. Useful to use in conjunction with CONTAINS and CONTAINSALL operators	map.**keys()** CONTAINS 'Luke'	1.0rc1
string	.left(<len>)	returns a substring of the original cutting from the begin and getting 'len' characters.	name.**left( 4 )** = 'Luke'	0.9.7
string	.length()	returns the length of the string. If the string is null 0 will be returned	name.**length()** > 0	0.9.7
string	.prefix('<string>')	Prefixes a string to another one	select name.**prefix('Mr. ')** from Profile	1.0rc1
string	.right(<len>)	returns a substring of the original cutting from the end of the string 'len' characters.	name.**right( 2 )** = 'ke'	0.9.7
collection	.size()	returns the size of the collection	children.**size()** > 0	0.9.7
string	.subString(<begin> [<len>](,) )	returns a substring of the original cutting from 'begin' and getting 'len' characters. 'begin' starts from 0 to string length -1	name.**substring( 0,1 )** = 'L'	0.9.7
string	.trim()	returns the original string removing white spaces from the begin and the end	name.**trim()** == 'luke'	0.9.7
record	.toJSON()	returns the record in JSON format	select @this.**toJson()** as json from Profile	0.9.8
string	.toUpperCase()	returns the string in upper case	name.**toUpperCase()** == 'LUKE'	0.9.7
string	.toLowerCase()	returns the string in lower case	name.**toLowerCase()** == 'luke'	0.9.7
maps	.values()	Returns the map's values as a separate collection. Useful to use in conjunction with CONTAINS and CONTAINSALL operators	map.**values()** CONTAINSALL ( name is not null)	1.0rc1

Functions

Since v.1.3.0 any database function can be called in SQL Select and Traverse statements.

Bundled functions

Name	Description	Example	Available since
{{{coalesce([the first field/value not null parameter. if no field/value is not null, returns null	SELECT coalesce(amount, amount2, amount3) from Account	1.3.0
ifnull(<field|value>, <return_value_if_null> [,<return_value_if_not_null>](,<field|value>]*)	Returns))}}}	Returns the passed field/value (or optional parameter return_value_if_not_null). If field/value is not null, otherwise it returns return_value_if_null	SELECT ifnull(salary, 0) from Account	1.3.0
flatten(<field>)	Extracts the collection in the field <field> and use it as result	select flatten( addresses ) from Account	1.0rc1
first(<field>)	Retrieves only the first item of multi-value fields (arrays, collections and maps). For non multi-value types just returns the value	select first( addresses ) from Account	1.2.0
count(<field>|**)	Counts the records that match the query condition. If ** is not used as a field, then the record will be counted only if the field content is not null	select count(*) from Account	0.9.25
min(<field>)	Returns the minimum value	select min(salary) from Account	0.9.25
max(<field>)	Returns the maximum value	select max(salary) from Account	0.9.25
avg(<field>)	Returns the average value	select avg(salary) from Account	0.9.25
sum(<field>)	Returns the sum of all the values returned	select average(salary) from Account	0.9.25
date([<format>](<date-as-string>,))	Returns a date formatting a string. <date-as-string> is the date in string format, and <format> is the date format following these [rules](http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html). If no format is specified, then the default database format is used	select from Account where created <= date('2012-07-02', 'yyyy-MM-dd')	0.9.25
sysdate()	Returns the current date time	select sysdate('dd-MM-yyyy') from Account	0.9.25
format(<format>, <arg1> [the values using the [http://pubs.opengroup.org/onlinepubs/9699919799/functions/printf.html printf()](,<argN>]*)	Formats) and [String.format()](http://download.oracle.com/javase/1.5.0/docs/api/java/lang/String.html) conventions. Look [here for more information](http://download.oracle.com/javase/1.5.0/docs/api/java/util/Formatter.html#syntax).	select format("%d - Mr. %s %s (%s)", id, name, surname, address) from Account	0.9.25
dijkstra(<sourceVertex>, <destinationVertex>, <weightEdgeFieldName> [the cheapest path between two vertices using the [http://en.wikipedia.org/wiki/Dijkstra's_algorithm Dijkstra algorithm](,<direction>])	Returns) where the **weightEdgeFieldName** parameter is the field containing the weight. Direction can be OUT (default), IN or BOTH	select dijkstra($current, #8:10, 'weight') from V	1.3.0
{{{shortestPath(, [the shortest path between two vertices. Direction can be OUT (default), IN or BOTH	select shortestPath(#8:32, #8:10, 'BOTH')	1.3.0
distance()	Computes the distance between two points in the globe using the Haversine algorithm. Coordinates must be as degrees	where distance(x, y,52.20472, 0.14056 ) <= 30	0.9.25
distinct(<field>)	Retrieves only unique data entries depending on the field you have specified as argument. The main differences with standard SQL DISTINCT are that in OrientDB is a function with parenthesis and only one field can be specified	select distinct(name) from City	1.0rc2
union(<field*>)	Works as aggregate or inline. If only one argument is passed than aggregates, otherwise executes, and returns, a UNION of the collections received as parameters. Works also with no collection values	select union(friends) from profile | select union(inEdges, outEdges) from OGraphVertex where label = 'test'	1.0rc2
intersect(<field*>)	Works as aggregate or inline. If only one argument is passed than aggregates, otherwise executes, and returns, the INTERSECTION of the collections received as parameters	select intersect(friends) from profile where jobTitle = 'programmer' | select intersect(inEdges, outEdges) from OGraphVertex	1.0rc2
difference(<field*>)	Works as aggregate or inline. If only one argument is passed than aggregates, otherwise executes, and returns, the DIFFERENCE between the collections received as parameters	select difference(tags) from book | select difference(inEdges, outEdges) from OGraphVertex	1.0rc2
set(<field>)	Add a value to a set. The first time the set is created. If <value> is a collection, then is merged with the set, otherwise <value> is added to the set	SELECT name, set(roles.name) as roles FROM OUser	1.2.0
list(<field>)	Add a value to a list. The first time the list is created. If <value> is a collection, then is merged with the list, otherwise <value> is added to the list	SELECT name, list(roles.name) as roles FROM OUser	1.2.0
map(<field>|<key>,<value>>)	Add a value to a map. The first time the map is created. If <value> is a map, then is merged with the map, otherwise the pair <key> and <value> is added to the map as new entry	SELECT map(name, roles.name) FROM OUser	1.2.0

Custom functions

The SQL engine can be extended with custom functions written with a Scripting language or via Java.

Database's function

Look at the Functions page.

Custom functions in Java

Before to use them in your queries you need to register:

    // REGISTER 'BIGGER' FUNCTION WITH FIXED 2 PARAMETERS (MIN/MAX=2)
    OSQLEngine.getInstance().registerFunction("bigger", new OSQLFunctionAbstract("bigger", 2, 2) {
      public String getSyntax() {
        return "bigger(<first>, <second>)";
      }
    
      public Object execute(Object[] iParameters) {
        if (iParameters[0] == null || iParameters[1] == null)
          // CHECK BOTH EXPECTED PARAMETERS
          return null;
    
        if (!(iParameters[0] instanceof Number) || !(iParameters[1] instanceof Number))
          // EXCLUDE IT FROM THE RESULT SET
          return null;
    
        // USE DOUBLE TO AVOID LOSS OF PRECISION
        final double v1 = ((Number) iParameters[0]).doubleValue();
        final double v2 = ((Number) iParameters[1]).doubleValue();
    
        return Math.max(v1, v2);
      }
    
      public boolean aggregateResults() {
        return false;
      }
    });

Now you can execute it:

    List<ODocument> result = database.command(new OSQLSynchQuery<ODocument>("select from Account where bigger( salary, 10 ) > 10")).execute();

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:

) and TRAVERSE

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 )	[SQLQuery SELECT](,])}}}	Returns) and [
$current	Current record to use in sub-queries to refer from the parent's variable	[SQLQuery SELECT](SQLTraverse	1.2.0
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.