SQL Query

SQL Queries

Orient supports the SQL language to execute queries against the engine. Look at the operators and Functions.

Syntax =

    SELECT [FROM <Target> [LET <Assignment>*](<Projections>]) [<Condition>*](WHERE) [BY <Field>](GROUP) [BY <Fields>* [ASC|DESC](ORDER)*] [<SkipRecords>](SKIP) [<MaxRecords>](LIMIT)

• Projections, optionals, are what you want to extract from the query as result set. Look at Projections. Available since 0.9.25.
• Target can be a class, a cluster, a single RID or a set of RIDs. Class is the class name where to execute the query, while specyfing cluster with the 'cluster:' prefix is the cluster name where to execute the query. Use one or more RIDs to specify one or a small set of record. Useful to navigate graphs starting from some root nodes
• WHERE condition is common to the other SQL commands and it's described apart.
• LET is the part that bind context variables to be used in projections, conditions or sub-queries
• GROUP BY is the same of the SQL standard syntax specifying the field where to group by. Current release supports only 1 field.
• ORDER BY is the same of the SQL standard syntax specifying fields with optionally the versus (default is ASCending)
• SKIP skips <SkipRecords> records from the result set. This is useful in Pagination together with LIMIT.
• LIMIT sets the maximum record the query can return to <MaxRecords>. This is useful in Pagination together with SKIP.

NOTE: Starting from 1.0rc7 the RANGE keyword has been removed. To execute range queries use the BETWEEN operator against @rid as explained in Pagination.

Projections

In the standard SQL projections are mandatory, but in OrientDB if 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 use projections a new temporary document is created and the @rid and @version of the original record are lost.

OrientDB has extended projections to allow special operators like {{{[example: a343bce104a2f4af5783f9adf61a93e4

The convention naming for the returned document's fields are:

• the field name for plain fields invoice -> invoice
• the first field name for chained fields, like invoice.customer.name -> invoice
• the name of the function for functions, like max(salary) -> max

If the target field already exists, a progressive number is used as 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: 3959a556e2b85ee21a4a5e17664dfa18

By using the dollar ($) as prefix you can access to the context variables. Every time you run a command it accesses to the context to read and write variables. Example to display the path and depth level of the SQLTraverse traversal on all the movies, up to the 5th level of depth:

    SELECT $path, $depth FROM ( TRAVERSE * FROM Movie WHERE $depth <= 5 )

Projections are available since 0.9.25.

Examples

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 equals 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

LET block

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.

Assign fields to reuse multiple time

OrientDB allows to cross 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 refer 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.

Sub-query

LET block allows also to assign to 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

Usage in projection

Context variables can be part of result set using them 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' )

Conclusion

To know more about other SQL commands look at SQL commands.