SQL - `SELECT`

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.

Syntax:

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 AS keyword; in current release aliases cannot be used in the WHERE condition, GROUP BY and ORDER BY (they will be evaluated to null)
FROM Designates 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 <target> use CLUSTER:<cluster-name> (eg. CLUSTER:person) or CLUSTER:<cluster-id> (eg. 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:
  - INDEXVALUES:<index> and INDEXVALUESASC:<index> sorts values into an ascending order of index keys.
  - INDEXVALUESDESC:<index> sorts the values into a descending order of index keys.
WHERE Designates conditions to filter the result-set.
LET Binds context variables to use in projections, conditions or sub-queries.
GROUP BY Designates field on which to group the result-set.
ORDER BY Designates the field with which to order the result-set. Use the optional ASC and DESC operators to define the direction of the order. The default is ascending. Additionally, if you are using a projection, you need to include the ORDER BY field in the projection. Note that ORDER BY works only on projection fields (fields that are returned in the result set) not on LET variables.
UNWIND Designates the field on which to unwind the collection. Introduced in version 2.1.
SKIP Defines 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 LIMIT.
LIMIT Defines the maximum number of records in the result-set. You may find this useful in pagination, when using it in conjunction with SKIP.
FETCHPLAN Defines how you want it to fetch results. For more information, see Fetching Strategy.
TIMEOUT Defines 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:
- RETURN Truncate the result-set, returning the data collected up to the timeout.
- EXCEPTION Raises an exception.
LOCK Defines the locking strategy. These are the available locking strategies:
- DEFAULT Locks the record for the read.
- RECORD Locks the record in exclusive mode for the current transaction, until the transaction commits or you perform a rollback operation.
PARALLEL Executes 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 PARALLEL execution useful on long running queries or queries that involve multiple cluster. For simple queries, using PARALLEL may cause a slow down due to the overhead inherent in using multiple threads.
NOCACHE Defines whether you want to avoid using the cache.

NOTE: Beginning with version 1.0 rc 7, the RANGE operator was removed. To execute range queries, instead use the BETWEEN operator against @RID. For more information, see Pagination.

Examples:

Return all records of the class Person, where the name starts with Luk:

orientdb> SELECT FROM Person WHERE name LIKE 'Luk%'

Alternatively, you might also use either of these queries:

orientdb> SELECT FROM Person WHERE name.left(3) = 'Luk'
orientdb> SELECT FROM Person WHERE name.substring(0,3) = 'Luk'

Return all records of the type !AnimalType where the collection races contains at least one entry where the first character is e, ignoring case:
```
orientdb> SELECT FROM animaltype WHERE races CONTAINS( name.toLowerCase().subString(
          0, 1) = 'e' )
```

Return all records of type !AnimalType where the collection races contains at least one entry with names European or Asiatic:

orientdb> SELECT * FROM animaltype WHERE races CONTAINS(name in ['European',
          'Asiatic'])

Return all records in the class Profile where any field contains the word danger:
```
orientdb> SELECT FROM Profile WHERE ANY() LIKE '%danger%'
```

Return any record at any level that has the word danger:

DEPRECATED SYNTAX

orientdb> 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:

orientdb> SELECT FROM Profile WHERE ANY() TRAVERSE(0, 3) ( 
          ANY().toUpperCase().indexOf('danger') > -1 )

Return all results on class Profile, ordered by the field name in descending order:
```
orientdb> SELECT FROM Profile ORDER BY name DESC
```

Return the number of records in the class Account per city:

orientdb> SELECT SUM(*) FROM Account GROUP BY city

Traverse records from a root node:

orientdb> SELECT FROM 11:4 WHERE ANY() TRAVERSE(0,10) (address.city = 'Rome')

Return only a limited set of records:

orientdb> SELECT FROM [#10:3, #10:4, #10:5]

Return three fields from the class Profile:

orientdb> SELECT nick, followings, followers FROM Profile

Return the field name in uppercase and the field country name of the linked city of the address:
```
orientdb> SELECT name.toUppercase(), address.city.country.name FROM Profile
```
Return records from the class Profile in descending order of their creation:
```
orientdb> SELECT FROM Profile ORDER BY @rid DESC
```
Return value of email which is stored in a JSON field data (type EMBEDDED) of the class Person, where the name starts with Luk:
```
orientdb> 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.

Projections

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 * wildcard.

orientdb> SELECT FROM Account

For all projections except the wildcard *, it creates a new temporary document, which does not include the @rid and @version fields of the original record.

orientdb> SELECT name, age FROM Account

The naming convention for the returned document fields are:

Field name for plain fields, like invoice becoming invoice.
First field name for chained fields, like invoice.customer.name becoming invoice.
Function name for functions, like MAX(salary) becoming max.

In the event that the target field exists, it uses a numeric progression. For instance,

orientdb> SELECT MAX(incoming), MAX(cost) FROM Balance

------+------
 max  | max2
------+------
 1342 | 2478
------+------

To override the display for the field names, use the AS.

orientdb> 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 Movie class.

orientdb> SELECT $path, $depth FROM ( TRAVERSE * FROM Movie WHERE $depth

`LET` Block

The 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.

Assigning Fields for Reuse

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.

orientdb> SELECT FROM Profile WHERE address.city.name LIKE '%Saint%"' AND 
          ( address.city.country.name = 'Italy' OR 
            address.city.country.name = 'France' )

Using the LET makes the query shorter and faster, because it traverses the relationships only once:

orientdb> 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.

Sub-query

The LET block allows you to assign a context variable to the result of a sub-query.

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

`LET` Block in Projection

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:

orientdb> SELECT $temp.name FROM Profile LET $temp = address.city WHERE $city.name 
          LIKE '%Saint%"' AND ( $city.country.name = 'Italy' OR 
          $city.country.name = 'France' )

Unwinding

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:

orientdb> 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 UNWIND:

orientdb> 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.

Execution planning

For details about query execution planning, please refer to SQL SELECT Execution

History

1.7.7: New target prefixes INDEXVALUES:, INDEXVALUESASC: and INDEXVALUESDESC: added.
1.7: PARALLEL keyword 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. PARALLEL execution is useful on long running queries or queries that involve multiple clusters. On simple queries, using PARALLEL can cause a slow down due to the overhead of using multiple threads.

Select

SQL - `SELECT`

Projections

`LET` Block

Assigning Fields for Reuse

Sub-query

`LET` Block in Projection

Unwinding

Execution planning

History

results matching ""

No results matching ""

SQL - SELECT

Projections

LET Block

Assigning Fields for Reuse

Sub-query

LET Block in Projection

Unwinding

Execution planning

History

results matching ""

No results matching ""

SQL - `SELECT`

`LET` Block

`LET` Block in Projection