Querying : RQL - Raven Query Language
Queries in RavenDB use a SQL-like language called "RavenDB Query Language," henceforth known as RQL.
It is designed to expose externally the RavenDB query pipeline in a way that is easy to understand, easy to use, and not overwhelming to the user.
When a query hits a RavenDB instance, the very first thing that happens is that it will be analyzed by the query optimizer. The role of the query optimizer is to determine what indexes should be used by this particular query.
In RavenDB, there are two types of queries.
We may have a dynamic query, such as
from Orders where ..., which gives the query optimizer full freedom with regards to which index that query will use.
Alternatively, a query can specify a specific index to be used, such as
from index 'Orders/ByCompany' where ..., which instructs RavenDB to use the
Queries are always going to use an index
In other databases, the query optimizer may fail to find a suitable index and fall back into querying using a full scan, RavenDB doesn't include support for full scans, and the query optimizer will create a new index for the query if it cannot find an relevant index.
You can read more about indexes here.
Queries in RavenDB will always use an index. RavenDB queries always use an index and can return results with the same speed regardless of the size of the data.
Indexing and queries in RavenDB
Indexing in RavenDB is a background operation, which means the new query will be waiting for the index to complete indexing (or timeout). But at the same time, queries that can be answered using the existing indexes will proceed normally using these indexes. When the new index has caught up, RavenDB will clean up all the old indexes that are now superseded by the new one.
Keywords and methods
The following keywords and methods are available in RQL:
- GROUP BY
- ORDER BY
With the following operators:
- <> or !=
- = or ==
- ALL IN
And the following values:
- string e.g. 'John' or "John"
- number (long and double) e.g. 17
- parameter e.g. $param1
declare gives you the ability to create a JS function that can be reused in
select (when projection is done). You can read more about it here.
from is used to determine the source data that should be used when a query is executed. You have two options:
This option is used to perform:
- Collection queries that are doing basic ID filtering only, e.g.
from Companies where id() == 'companies/1-A'where there is no need to query an index, we can return the document from the storage directly
- Dynamic queries that are being executed against Auto Index
In order to query all documents, the
@all_docs keyword can be used:
from @all_docs where FirstName = 'Laura'
from @all_docs where id() = 'companies/1-A'.
from INDEX <index-name>
This option is used to perform RQL operations against a given [static index].
group by is used to create an aggregation query. Please refer to the article about dynamic group by queries to find out more.
where is used to filter-out the documents from final results.
Operator: >= <= <> != < > = ==
The operators above are considered basic and self-explanatory. They work on all value types including 'numbers' and 'strings'.
The simplest example would be to return results with the field value equal to a given input. If you want to return a document from the
@companies collection (more about collection can be read here), you need to execute the following query:
from Companies where Name = 'The Big Cheese'
Filtering on nested properties is also supported, so in order to return all companies from 'Albuquerque' we need to execute following query:
from Companies where Address.City = 'Albuquerque'
between returns results inclusively, and the type of border values used must match. It works on both 'numbers' and 'strings' and can be substituted with the
<= operators (see the example below).
from Products where PricePerUnit between 10.5 and 13.0
from Products where PricePerUnit >= 10.5 and PricePerUnit <= 13.0
in is validating if a given field contains passed values. It will return results if a given field matches any of the passed values.
from Companies where Name IN ('The Big Cheese', 'Unknown company name')
from Orders where Lines.ProductName in ('Chang', 'Spegesild', 'Unknown product name')
Operator: ALL IN
This operator checks if all passes values are matching a given field. Due to its mechanics, it is only useful when used on array fields.
The following query will yield no results in contrast to an 'in' operator.
from Orders where Lines.ProductName all in ('Chang', 'Spegesild', 'Unknown product name')
but removing the 'Unknown product name' will give you orders that only contains products with both 'Chang' and 'Spegesild' names
from Orders where Lines.ProductName all in ('Chang', 'Spegesild')
Binary operators: AND OR NOT
Binary operators can be used to build more complex statements. The
NOT operator can only be used with one of the other binary operators creating
OR NOT or
AND NOT ones.
from Companies where Name = 'The Big Cheese' OR Name = 'Richter Supermarkt'
from Orders where Freight > 500 AND ShippedAt > '1998-01-01'
from Orders where Freight > 500 AND ShippedAt > '1998-01-01' AND NOT Freight = 830.75
Subclauses: ( )
Subclauses can be used along with binary operators to build even more complex logical statements. They are self-explanatory so no example will be given.
To perform sorting, the
order by must be used. If you are interested in this subject, please read our dedicated sorting article here.
When there is a need to use data from an external document in projection,
load can be used. Please refer to the following projection article to find out more about it.
Projections can be performed by using
select. Please read our dedicated projection article here.
To patch documents on the server-side, use
update with the desired JS script that will be applied to any documents matching the query criteria. For more information, please refer to our patching article.