OrientDB supports the SQL language to execute queries against the database engine. For more information, see operators and functions. For more information on the differences between this implementation and the SQL-92 standard, please refer to this section.
SELECT [ <Projections> ] [ FROM <Target> [ LET <Assignment>* ] ] [ WHERE <Condition>* ] [ GROUP BY <Field>* ] [ ORDER BY <Fields>* [ ASC|DESC ] * ] [ UNWIND <Field>* ] [ SKIP <SkipRecords> ] [ LIMIT <MaxRecords> ] [ FETCHPLAN <FetchPlan> ] [ TIMEOUT <Timeout> [ <STRATEGY> ] [ LOCK default|record ] [ PARALLEL ] [ NOCACHE ]
<Projections>Indicates the data you want to extract from the query as the result-set. Note: In OrientDB, this variable is optional. In the projections you can define aliases for single fields, using the
ASkeyword; in current release aliases cannot be used in the WHERE condition, GROUP BY and ORDER BY (they will be evaluated to null)
FROMDesignates the object to query. This can be a class, cluster, single Record ID, set of Record ID's, or (beginning in version 1.7.7) index values sorted by ascending or descending key order.
- When querying a class, for
<target>use the class name.
- When querying a cluster, for
CLUSTER:12). This causes the query to execute only on records in that cluster.
- When querying record ID's, you can specific one or a small set of records to query. This is useful when you need to specify a starting point in navigating graphs.
- When querying indexes, use the following prefixes:
INDEXVALUESASC:<index>sorts values into an ascending order of index keys.
INDEXVALUESDESC:<index>sorts the values into a descending order of index keys.
- When querying a class, for
WHEREDesignates conditions to filter the result-set.
LETBinds context variables to use in projections, conditions or sub-queries.
GROUP BYDesignates field on which to group the result-set.
ORDER BYDesignates the field with which to order the result-set. Use the optional
DESCoperators to define the direction of the order. The default is ascending. Additionally, if you are using a projection, you need to include the
ORDER BYfield in the projection. Note that ORDER BY works only on projection fields (fields that are returned in the result set) not on LET variables.
UNWINDDesignates the field on which to unwind the collection. Introduced in version 2.1.
SKIPDefines the number of records you want to skip from the start of the result-set. You may find this useful in pagination, when using it in conjunction with
LIMITDefines the maximum number of records in the result-set. You may find this useful in pagination, when using it in conjunction with
FETCHPLANDefines how you want it to fetch results. For more information, see Fetching Strategy.
TIMEOUTDefines the maximum time in milliseconds for the query. By default, queries have no timeouts. If you don't specify a timeout strategy, it defaults to
EXCEPTION. These are the available timeout strategies:
RETURNTruncate the result-set, returning the data collected up to the timeout.
EXCEPTIONRaises an exception.
LOCKDefines the locking strategy. These are the available locking strategies:
DEFAULTLocks the record for the read.
RECORDLocks the record in exclusive mode for the current transaction, until the transaction commits or you perform a rollback operation.
PARALLELExecutes the query against x concurrent threads, where x refers to the number of processors or cores found on the host operating system of the query. You may find
PARALLELexecution useful on long running queries or queries that involve multiple cluster. For simple queries, using
PARALLELmay cause a slow down due to the overhead inherent in using multiple threads.
NOCACHEDefines whether you want to avoid using the cache.
NOTE: Beginning with version 1.0 rc 7, the
RANGEoperator was removed. To execute range queries, instead use the
@RID. For more information, see Pagination.
Return all records of the class
Person, where the name starts with
SELECT FROM Person WHERE name LIKE 'Luk%'
Alternatively, you might also use either of these queries:
SELECT FROM Person WHERE name.left(3) = 'Luk'orientdb>
SELECT FROM Person WHERE name.substring(0,3) = 'Luk'
Return all records of the type
!AnimalTypewhere the collection
racescontains at least one entry where the first character is
e, ignoring case:
SELECT FROM animaltype WHERE races CONTAINS( name.toLowerCase().subString( 0, 1) = 'e' )
Return all records of type
!AnimalTypewhere the collection
racescontains at least one entry with names
SELECT * FROM animaltype WHERE races CONTAINS(name in ['European', 'Asiatic'])
Return all records in the class
Profilewhere any field contains the word
SELECT FROM Profile WHERE ANY() LIKE '%danger%'
Return any record at any level that has the word
SELECT FROM Profile WHERE ANY() TRAVERSE( ANY() LIKE '%danger%' )
Return any record where up to the third level of connections has some field that contains the word
danger, ignoring case:
SELECT FROM Profile WHERE ANY() TRAVERSE(0, 3) ( ANY().toUpperCase().indexOf('danger') > -1 )
Return all results on class
Profile, ordered by the field
namein descending order:
SELECT FROM Profile ORDER BY name DESC
Return the number of records in the class
SELECT SUM(*) FROM Account GROUP BY city
Traverse records from a root node:
SELECT FROM 11:4 WHERE ANY() TRAVERSE(0,10) (address.city = 'Rome')
Return only a limited set of records:
SELECT FROM [#10:3, #10:4, #10:5]
Return three fields from the class
SELECT nick, followings, followers FROM Profile
Return the field
namein uppercase and the field country name of the linked city of the address:
SELECT name.toUppercase(), address.city.country.name FROM Profile
Return records from the class
Profilein descending order of their creation:
SELECT FROM Profile ORDER BY @rid DESC
Return value of
data(type EMBEDDED) of the class
Person, where the name starts with
SELECT data.email FROM Person WHERE name LIKE 'Luk%'
Beginning in version 1.7.7, OrientDB can open an inverse cursor against clusters. This is very fast and doesn't require the classic ordering resources, CPU and RAM.
In the standard implementations of SQL, projections are mandatory. In OrientDB, the omission of projects translates to its returning the entire record. That is, it reads no projection as the equivalent of the
SELECT FROM Account
For all projections except the wildcard
*, it creates a new temporary document, which does not include the
@version fields of the original record.
SELECT name, age FROM Account
The naming convention for the returned document fields are:
- Field name for plain fields, like
- First field name for chained fields, like
- Function name for functions, like
In the event that the target field exists, it uses a numeric progression. For instance,
SELECT MAX(incoming), MAX(cost) FROM Balance------+------ max | max2 ------+------ 1342 | 2478 ------+------
To override the display for the field names, use the
SELECT MAX(incoming) AS max_incoming, MAX(cost) AS max_cost FROM Balance---------------+---------- max_incoming | max_cost ---------------+---------- 1342 | 2478 ---------------+----------
With the dollar sign
$, you can access the context variables. Each time you run the command, OrientDB accesses the context to read and write the variables. For instance, say you want to display the path and depth levels up to the fifth of a
TRAVERSE on all records in the
SELECT $path, $depth FROM ( TRAVERSE * FROM Movie WHERE $depth <= 5 )
LET block contains context variables to assign each time OrientDB evaluates a record. It destroys these values once the query execution ends. You can use context variables in projections, conditions, and sub-queries.
OrientDB allows for crossing relationships. In single queries, you need to evaluate the same branch of the nested relationship. This is better than using a context variable that refers to the full relationship.
SELECT FROM Profile WHERE address.city.name LIKE '%Saint%"' AND ( address.city.country.name = 'Italy' OR address.city.country.name = 'France' )
LET makes the query shorter and faster, because it traverses the relationships only once:
SELECT FROM Profile LET $city = address.city WHERE $city.name LIKE '%Saint%"' AND ($city.country.name = 'Italy' OR $city.country.name = 'France')
In this case, it traverses the path till
address.city only once.
LET block allows you to assign a context variable to the result of a sub-query.
SELECT FROM Document LET $temp = ( SELECT @rid, $depth FROM (TRAVERSE V.OUT, E.IN FROM $parent.current ) WHERE @class = 'Concept' AND ( id = 'first concept' OR id = 'second concept' )) WHERE $temp.SIZE() > 0
You can use context variables as part of a result-set in projections. For instance, the query below displays the city name from the previous example:
SELECT $temp.name FROM Profile LET $temp = address.city WHERE $city.name LIKE '%Saint%"' AND ( $city.country.name = 'Italy' OR $city.country.name = 'France' )
Beginning with version 2.1, OrientDB allows unwinding of collection fields and obtaining multiple records as a result, one for each element in the collection:
SELECT name, OUT("Friend").name AS friendName FROM Person--------+------------------- name | friendName --------+------------------- 'John' | ['Mark', 'Steve'] --------+-------------------
In the event if you want one record for each element in
friendName, you can rewrite the query using
SELECT name, OUT("Friend").name AS friendName FROM Person UNWIND friendName--------+------------- name | friendName --------+------------- 'John' | 'Mark' 'John' | 'Steve' --------+-------------
NOTE: For more information on other SQL commands, see SQL Commands.
For details about query execution planning, please refer to SQL SELECT Execution
- 1.7.7: New target prefixes
PARALLELkeyword added to execute the query against x concurrent threads, where x is the number of processors or cores found on the operating system where the query runs.
PARALLELexecution is useful on long running queries or queries that involve multiple clusters. On simple queries, using
PARALLELcan cause a slow down due to the overhead of using multiple threads.