Orient supports the SQL language to execute queries against the databas engine. Take a look at the operators and Functions. To learn the main differences in comparison to 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 executes the query within that cluster only. You can fetch records not from a cluster but instead 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. This is a useful in order to specify a starting point when navigating graphs.<SkipRecords> the specified number of records starting at the beginning of the result set. This is useful for Pagination when used in conjunction with LIMIT.<MaxRecords>. This is useful for Pagination when used in conjunction 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 up until 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. In OrientDB if it's omitted, the entire record set is returned. It is the equivalent of the * keyword. Example:
SELECT FROM Account
With all projections except the wildcard "*", a new temporary document is created and the @rid and @version of the original record will not be included.
SELECT name, age FROM Account
The conventional naming for the returned document's fields are:
invoice -> invoiceinvoice.customer.name -> invoicemax(salary) -> maxIf 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 context variables. Each time you run a command, OrientDB 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 each 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.
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.