Filtering

In the previous section, we learned from, first, and essential RQL keyword. It will be a part of every RQL query you make. We saw it used with a collection name, from Employees, to return all nine Employee documents.

Executing this query will show that Andrew is the only employee of Northwind traders with this last name, as shown in Figure 3-5.

A screenshot of the query page has 2 panels. The text in the top query panel reads, from employees where last name equals fuller. The results panel at the bottom depicts a document in which the last name is fuller.

Executing this query will produce the same result of just one document – Andrew Fuller has a unique first and last name on the company level .

which will produce the same result.

Query by Nonexistent Property

In Listing 3-4, we made an intentional typo – FistName. If you are a developer accustomed to relational databases, you will expect to receive an error upon executing this query.

However, in RavenDB, an attempt to filter a collection by the nonexistent property name will not produce any errors. You will get an empty result set. Why is that? If you recall, RavenDB is a schemaless database. In relational databases, a schema is a set of mandatory fields for each table. The schemaless nature of RavenDB collections means the absence of any compulsory structure imposed upon documents belonging to the same collection. There is no such thing as a set of mandatory properties such documents must have. Hence, filtering by any property name you can think of will not produce an error – it may only make an unexpected empty result set.

Query by Non-string Properties

Filtering by Complex Properties

Query from Listing 3-6 will select all employees living in the United States. Pay attention to the format of the property name Address.Country – looking at the JSON from Listing 3-5, you will observe that Country is a nested property within Address property.

Transforming the nested structure of JSON properties into a linear form suitable for filtering queries is straightforward. You will write parent property on the first level (in this case, Address), and then add desired property from the second level (in this case, Country), separated by a dot.

The same approach is applied with properties on deeper levels. Hence, to filter by Latitiude property visible in Listing 3-5, we will first state top-level ancestor Address and then descend over Location to the Latitude. Every time we go one level deeper, we will add a dot as a separator between property names. Hence, our final flattened form is Address.Location.Latitude. When reading this concatenated property from left to right, you descend one level every time you run into a dot.

and will return all employees living along specified Latitude.

Filtering by Id

will return an empty result set, even though we can inspect that employee with id employees/2-A exists.

Why is this the case? As we saw in the previous chapter, an identifier is a unique property called @id located within @metadata. However, this is a default location of an identifier which is also configurable. Because of this, you need to use function id() that will return the name of the property holding the document identifier .

and results will show this is our old friend, Andrew Fuller.

Cross-Collection Query

will find all documents across all collections with a FirstName property containing value Andrew. This query result is the same as Listing 3-3, where we specified the collection name. The reason for this is apparent – only Employee documents have FirstName property.

we will get a result set consisting of documents from various collections, as can be seen in Figure 3-6.

A screenshot of the results panel on the query page has a list of documents under the label, index auto slash all docs slash by address country. The documents are listed under the column labels: preview, I d, name, contact, address, phone, fax, home page, external I d, last name, and first name.

This query returned 22 documents in total from Suppliers, Companies, and Employees collection. All these documents represent business entities located in the United States.

will return a total of 129 documents that contain Country property within Address property. However, pay attention that this will also return those documents containing a null value for a specific property. From the perspective of RavenDB, nothing is distinguishing null – it is a regular value property can have. Hence, exists() will check the presence of a specified property, ignoring its content .

Inequality Query

will select all Employees where the first name is not Andrew.

Logical Operators

IN operator accepts a list of values and will return true if the stated property has any of the values from the list.

This query will return two products, products/34-A and products/67-A, which have 16 units in stock and cost 14 per unit.

and shorten it. However, a query like this will always return an empty result set – you cannot have a property with two values at once. And, indeed, the single value of a simple property cannot, but complex property that contains multiple values can.

Figure 3-7 shows regions/1-A document with a Territories property which is a collection of territories.

A screenshot depicts a document titled regions slash 1 A. The options below the title are, save, clone, and delete. The document depicts 19 lines of code.

We can observe two interesting features here. The first one is ALL IN operator that provides a way to match multiple values against an array. The second one is the expression Territories[].Name which is taking all elements of Territories collection and extracting Name property from every element.

A typical use case of the ALL IN operator is selecting all documents tagged with a specified set of tags .

Range Queries

So far, we have been using equality and inequality operator to filter by exact matches. It is also possible to use additional operators to create range queries .

will list all products with 26 or more units in stock. We will leave as an exercise usage of analogous operators < and <=.

lists all products that have one, two, or three units in stock.

Note that BETWEEN is inclusive on the lower and higher end of an interval .

Casing

Even though you might expect no results, since we capitalized all letters in the name, this query will return Andrew Fuller as the only result. Why is that?

RavenDB defaults to case-insensitive matching in queries. In this case, the value of the filtering condition was different from the actual property in the document, but Andrew and ANDREW matched since RavenDB ignores casing differences. This case insensitivity was a conscious decision since, in most scenarios, case-insensitive comparison of strings will produce results as you desire.

Full-Text Searching

The ability to perform a full-text search over data is one of the standard features in modern applications. This kind of search will inevitably be present in most of the applications you will be building over time. Fortunately, this is one of the areas where RavenDB excels. We will provide more information in chapters to come, but for now, let’s just say that RavenDB uses Lucene.net internally for indexing purposes. Lucene is one of the best indexing engines available today, and over the years, it has established itself as a reliable and standard solution. As a part of an infrastructure, it has been present in several brand-name products, like Solr and Elastic Search. So, RavenDB can provide you with first-class full-text searching capabilities and eliminate a need for any additional solution that would provide you with this.

This query uses the StartsWith() function that will match the content of the FirstName property of every employee with the prefix an. After executing it, you will get results, as shown in Figure 3-8.

A screenshot of the results panel in the query window has 2 documents under the label, index auto slash employees slash by first name. The first names in the 2 documents are Anne and Andrew, respectively.

We got two results, two employees, both having first names that start with “an.” Note that in this case, we have a case-insensitive query as well.

When executed, this query will return two companies, “Let’s Stop N Shop” and “QUICK-Stop” as shown in Figure 3-9.

A screenshot of the results panel in the query window has 2 documents under the label, index auto slash companies slash by search name. The names in the 2 documents are let’s stop N shop and quick stop respectively.

Both of these companies contain the word “stop” as a part of their name .

It is clear what happens when we perform equality filtering, where exact matching is done, but what happens behind the scene in this case, where partial matching on the arbitrary position can happen? To provide this functionality, RavenDB will take the Name of the company and apply tokenization: the name will be split into words, and every such word will be indexed. As an example, the company name “Let’s Stop N Shop” would be divided into four tokens: “Let”, “Stop”, “N”, “Shop”. After you execute a full-text search query, RavenDB will match your search term against a set of tokens and show you results. So, you could say that full-text search is still exact matching, but not against full property value – instead, components (tokens) are matched.

will search for all companies that contain “monde” or “cheese” in their name, returning three companies as a result set, as shown in Figure 3-10.

A screenshot of the results panel in the query window depicts 3 documents under the label, index auto slash companies slash by search name. The names in the 3 documents are spécialités du monde, Du monde entire, and the big cheese respectively.

you will get a list of companies located in London or Sweden. As you can see, all properties of the Address are indexed, so the full-text search is simultaneously checking both City and Country fields .

Sorting

After executing it, you will get a result as shown in Figure 3-11.

A screenshot of the results panel in the query window has 9 documents under the label, employees. The documents are listed under the column labels: preview, I d, last name, first name, title, address, and hired at.

will apply secondary sorting by the first name in all those cases when two employees have the same last name.

Executing this query results in sorted products, as shown in Figure 3-12 .

A screenshot of the results panel in the query window has a list of documents. The documents are listed under the column labels: preview, I d, name, supplier, category, quantity per unit, and price per unit.

However, looking closely at the column PricePerUnit reveals it is not sorted in the descending order as we expect. Why is this happening , and how to correctly sort Products by this column?

The cause of this lies in the way that RavenDB treats stored data. Fields are not typed, and without us expressing intention, RavenDB will default to lexical ordering, i.e., fields are treated as strings by default. With descending lexical ordering, 81 will come before 9, and that explains the sorting order we got.

Paging

will filter companies from the United States, order them by name in ascending order, skip the first five, and take the next five .

Projecting Results

Executing this query will produce results as shown in Figure 3-13.

A screenshot of the results panel in the query window has 9 documents under the label, index auto slash employees slash by last name. The last names in the list of documents are in ascending order.

As you can see , select is very similar to the SQL variant – it will select and return only a subset of fields.

ShipTo object is selected as it is, and Products property contains projection composed of product name selected from all order lines.

Aggregation

Aggregation is the process of grouping data. In this section, we will examine how RQL can provide data aggregations and enable you to sum up your documents in various ways.

If you recall, every order has Company property that contains the Id of the company, as shown in Figure 3-14.

A screenshot of a table has 5 columns and 10 rows. The column labels are I d, company, employee, freight, and ordered at.

As you might have expected, an appropriate keyword for this is group indeed. After executing this query, we will get results as shown in Figure 3-15.

A screenshot of the results panel in the query window has 7 documents under the label, index auto slash orders slash by count reduced by company. The documents are listed under the column labels: preview, company, and count.

Compared with the previous query, you will notice a new line load Company as c. Load command will instruct RQL to load the document with id contained in property Company and assign it alias c. After this is done, we can access the company’s name in the last line by referencing c.Name. Finally, we can show our users that “Save-a-lot Markets,” “Ernst Handel,” and “QUICK-Stop” are 3 companies with more than 20 orders.

In most applications, aggregation queries are the primary tool for fetching helpful information from the database. RQL aggregating basics we just demonstrated are one way of summing up data. RavenDB has another much more powerful mechanism, which will be covered in chapters to come .

Handling Relationships

In the previous chapter on Modeling, we learned about suitable and appropriate modeling principles that will result in documents with a balanced level of independency, isolation, and coherency. However, even with such documents, you will still need to combine them into view models that combine properties from two or more documents into a form suitable for visual representation . Let’s look at how RavenDB can support you in such scenarios.

Include

Besides using projections , there is one more way to access related documents. RQL provides include, which is another way to reduce the number of round trips you need to make to fetch complete information:

from Orders

where id() = 'orders/830-A'

include Company, Employee

This query will return a complete order document, but also, in the same round trip to the database, it will produce two additional collections, as shown in Figure 3-16.

A screenshot of the results panel in the query window has a document under the label, orders. The document is listed under the column labels: preview, I d, company, employee, freight, and lines.

At the bottom of Figure 3-16, you can see two more result sets. Since we have just one order returned, these two collections contain just one company and just one employee.

Include syntax is intended to be used when you want to fetch the complete document from the database and pull along all related documents in the same round trip to the database. You will then perform some additional operations on these documents without being forced to make any more requests to the database.