Orient supports the SQL language to execute queries against the engine. Take a look at the operators and Functions. To know the main differences with the SQL-92 standard, take a look at: OrientDB SQL.
SELECT [<Projections>] [FROM <Target> [LET <Assignment>*]]
[WHERE <Condition>*]
[GROUP BY <Field>*]
[ORDER BY <Fields>* [ASC|DESC] *]
[SKIP <SkipRecords>]
[LIMIT <MaxRecords>]
[FETCHPLAN <FetchPlan>]
[TIMEOUT <Timeout> [<STRATEGY>]
[LOCK default|record]
[PARALLEL]
cluster:
prefix is the cluster name where to execute the query. Also you can fetch records not from cluster but from an index using the following prefixes: indexvalues:
, indexvaluesasc:
or indexvaluesdesc:
. If you are using indexvalues:
or indexvaluesasc:
prefix records will be sorted in ascending order of index keys. If you are using indexvaluesdesc:
prefix records will be sorted in descending order of index keys. Use one or more RIDs to specify one or a small set of records. It's useful in order to navigate graphs starting from some root nodes<SkipRecords>
records from the result set. This is useful in Pagination together with LIMIT
.<MaxRecords>
. This is useful in Pagination together with SKIP.FETCHPLAN out:3
to pre-fetch up to 3rd level under out
field. Since v1.5.EXCEPTION
is used. Strategies are:RETURN
, truncate the result set returning the data collected till the timeoutEXCEPTION
, default one, throws an exception if the timeout has been reachedNOTE: Starting from 1.0rc7 the RANGE
keyword has been removed. To execute range queries use the BETWEEN
operator against @rid
as explained in Pagination.
In the standard SQL, projections are mandatory, but in OrientDB if it's omitted, the entire record is returned, namely the equivalent of *
keyword, but the record's identity and version are preserved. Example:
SELECT FROM Account
By using the wildcard *
the fields retrieved will be the same but the RID and version will be lost:
SELECT * FROM Account
So remember that when using projections, a new temporary document is created and the @rid
and @version
of the original record will be lost.
The conventional naming for the returned document's fields are:
invoice -> invoice
invoice.customer.name -> invoice
max(salary) -> max
If the target field already exists, a progressive number is used as a prefix. Example:
SELECT max(incoming), max(cost) FROM Balance
Will return a document with the field max
and max2
.
To override the field name, use AS
. Example:
SELECT max(incoming) AS max_incoming, max(cost) AS max_cost FROM Balance
By using the dollar ($
) as a prefix, you can access to the context variables. Every time you run a command, it accesses the context to read and write variables. Here's an example to display the path and depth level of the traversal on all the movies, up to the 5th level of depth:
SELECT $path, $depth FROM ( TRAVERSE * FROM Movie WHERE $depth <= 5 )
Get all the records of type Person
where the name starts with Luk
:
select * from Person where name like 'Luk%'
or
select * from Person where name.left(3) = 'Luk'
or
select * from Person where name.substring(0,3) = 'Luk'
Get all the records of type !AnimalType
where the collection races
contains at least one entry where the first character of the name, ignoring the case, is equal to e
:
select * from animaltype where races contains (name.toLowerCase().subString(0,1) = 'e')
Get all the records of type !AnimalType
where the collection races
contains at least one entry with name European
or Asiatic
:
select * from animaltype where races contains (name in ['European','Asiatic'])
Get all the records of type Profile
where any field contains the word danger
:
select from profile where any() like '%danger%'
Get any record at any level that has the word danger
:
select from profile where any() traverse ( any() like '%danger%' )
Get all the records where up to the 3rd level of connections has some field that contains the word danger
ignoring the case:
select from Profile where any() traverse( 0,3 ) ( any().toUpperCase().indexOf( 'danger' ) > -1 )
Order the result set by the name
in descending order:
select from Profile order by name desc
Returns the total of records per city:
select sum(*) from Account group by city
Traverse record starting from a root node:
select from 11:4 where any() traverse(0,10) (address.city = 'Rome')
Query only a set of records:
select from [#10:3, #10:4, #10:5]
Select only three fields from Profile:
select nick, followings, followers from Profile
Select the name
field in upper-case and the country name
of the linked city of the address:
select name.toUppercase(), address.city.country.name from Profile
Order by record creation. Starting from 1.7.7, using the expression "order by @rid desc", allows OrientDB to open an Inverse cursor against clusters. This is extremely fast and doesn't require classic ordering resources (RAM and CPU):
select from Profile order by @rid desc
The LET
block contains the list of context variables to assign every time a record is evaluated. These values are destroyed once the query execution ends. Context variables can be used in projections, conditions and sub-queries.
OrientDB allows crossing relationships, but if in a single query you need to evaluate the same branch of nested relationship, it's definitely better using a context variable that refers to the full relationship every time.
Example:
SELECT FROM Profile
WHERE address.city.name like '%Saint%"' and
( address.city.country.name = 'Italy' or address.city.country.name = 'France' )
Using LET becomes shorter and faster, because the relationships are traversed 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 the path till address.city
is traversed only once.
LET block allows you to assign a context variable the result of a sub-query. Example:
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
Context variables can be part of result set used in Projections. The example below displays the city name of 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' )
To know more about other SQL commands, take a look at SQL commands.
New targets indexvalues:
, indexvaluesasc:
, indexvaluesdesc:
are added.