The Couchbase Lite 2.0 Release, now in Developer Preview brings a plethora of new features and enhancements. We discussed one such enhancement, namely the BLIP replication protocol between Couchbase Lite 2.0 and Sync Gateway (v1.5 and above) in an earlier blog post on Replication 2.0. In this blog post, we will introduce you to another major feature – the Couchbase Lite Query Interface. The new query interface is based on N1QL, Couchbase’s declarative query language that extends SQL for JSON. If you are familiar with SQL, you will feel right at home with the semantics of the new API.

This is the first in a series of posts that will introduce you to the query interface. In this post, we will introduce you to the basics. Stay tuned for future blog posts on advanced querying capabilities including Full Text Search.

Everything discussed in this post is based on Couchbase Mobile 2.0 Developer Preview–019. So there may be enhancements to the API before we go GA. We will repost with updates.

Background

If you were using 1.x versions of Couchbase Mobile, you are probably familiar Map-Views for creating indexes and queries. In 2.0, you no longer have to create views and map functions! Instead, you can use a new Query Builder interface to construct your queries and indexes. The new query interface is simpler to use and much more powerful in comparison. We will discover some of it’s features in this post.

Sample Project

While the examples discussed here use Swift for iOS, note that barring some minor differences, the same query interface is supported on the Android and Windows platforms as well. So with some minor tweaks, you should be able to reuse the examples in this post when working with other platforms.

Follow instructions below to get a sample Swift Playground to try out the query interface

  • Clone the iOS Swift Playground from GitHub
  • Follow the installation instructions in the corresponding README file to build and execute the playground.

Sample Data Model

We shall use the Travel Sample database located here

The sample data set includes several types of documents as identified by the type property in the document. We will focus on documents of type “hotel” and “landmark”. The JSON document model is shown below. For simplicity, we have omitted some of the properties from the model below.

Query Data Model

The Basics

Opening / Creating a Database

For all the queries below, we will use the Database API to open/create CouchbaseLite Database.

Indexes

To speed up read queries, you can create Indexes on properties that you will query on. The performance improvement would be significant on large datasets. Of course, be aware that there will be an increase in storage needs in order to store the indexes and performance of writes can also be impacted. So be cautious of creating too many indexes.

The following example creates a ValueIndex on the type property of a Document

The following example creates a ValueIndex on multiple properties of a Document

Fetching documents from database

A Query in Couchbase Lite is constructed using the Query builder API.

The query below fetches all documents from specfied database. For every document matching the query, all the properties associated with the document are fetched.

Fetching documents from database with pagination

This is the structure of a simple query which fetches limit number of documents from database starting at specified offset. For every document matching the query, all the properties associated with the document are fetched.

Return Values using SelectResult

A SelectResult represents a single return value of the query statement. Documents in Couchbase Lite comprise of the document properties specified as a Dictionary of Key-Value pairs and associated metadata. The metadata consists of document Id and sequence Id associated with the Document. When you query for a document, the document metadata is not returned by default. You will need to explicitly query for the metadata.

  • SelectResult.all()
    • Returns all properties associated with a Document
  • SelectResult(Expression)
    • Returns properties of a Document based on the Expression. We discuss the various types of expressions a litte later.
  • SelectResult.expression(Expression.meta().id)
    • Returns document Id
  • SelectResult.expression(Expression.meta().sequence)
    • Returns sequence Id (used in replications)

You can specify a comma separated list of SelectResult expressions in the select statement of your Query.
For instance the following select statement queries for the document Id as well as the type and name properties of the document

Query Expression

A Query Expression is used for constructing a Query Statement

Couchbase Lite includes support for the following Expression Types.

  • Property
  • Aggregate
  • String
  • Collation
  • Regex
  • FTS
  • Null Check Operator
  • Arithmetic Expressions
  • Metadata
  • Pattern Matching
  • Comparison
  • Collection
  • Function
  • Parameter
  • Quantified
  • Bitwise Operator

Processing Query Responses

The results of the query run is an array where every member of the array is an Dictionary / Map corresponding to a document that satisfies the query.

  • If you are querying for all the properties of the document using SelectResult.all(), then each member of the response array is a Key-Value pair, where the database name is the key and the dictionary corresponding to the Document is the value for the key.For instance,consider the query

    The results would look something like this (where “travel-sample” is the name of the database )

    The code snippet below is an example (in Swift) of how you would process the above results to get the details of the document object
  • If you are querying for Id of the document using SelectResult.expression(Expression.meta().id), then each member of the response array is a Key-Value pair, with "_id" as the key and the the document Id as the value.
    For instance,consider the query

    The results would look something like this –

    The code snippet below is an example (in Swift) of how you would process the above results to get the details of the document object using the document Id
  • If you are querying for specific property / properties of the document using for instance, SelectResult.expression(Expression.property("type"), then each member of the response array is a Key-Value pair, with the name of the property / properties as the key(s) and the corresponding property values as the valueFor instance,consider the query

    The results would look something like this –

    The code snippet below is an example (in Swift) of how you would process the above results to get the property values that you queried for

    You can query for both the Document metadata and properties at the same time by using a comma separated list of SelectResult clauses, then each member of the response array is a Key-Value pairs as discussed earlier

    For instance,consider the query

    The results would look something like this –

    The code snippet below is an example (in Swift) of how you would process the above results to get the property values that you queried for

Introducing the Where clause

Similar to SQL, you can use the where clause to filter the documents to be returned as part of the query. The select statement takes in an Expression. You can chain any number of Expressions in order to implement sophisticated filtering capabilities.

Filtering documents based on specific property

In the example below, we use Property Expression type in conjunction with Comparison Expressions type to filter documents based on a specific document property. The example below shows the equalTo comparison expression.

Note: When referencing a property in a Property Expression,we can use key paths (in dotted notation) to specify the path to a nested property.

The list of supported comparison operators include , among others –
* lessThan
* lessThanOrEqualTo
* greaterThan
* greaterThanOrEqualTo
* equalTo
* notEqualTo

Filtering documents using logical Expressions

We can use Logical Expression to chain together several comparison expressions. In the example below, we fetch documents of type hotel whose country property is equal To “United States” or “France” and whose vacancy property is true. In other words, we are fetching all hotels in the United States or France that have a vacancy.

Pattern Matching

The like and regex expressions can be used for string matching. The former is typically used for case insensitive matches while the regex expressions is used for case sensitive matches.

Exact Match

In the example below, we are looking for documents of type “landmark” where the name property exactly matches the string “Royal engineers museum”. Note that since like does a case insensitive match, the following query will return “landmark” type documents with name matching “Royal Engineers Museum”, “royal engineers museum”, “ROYAL ENGINEERS MUSEUM” and so on.

Wildcard Match

We can use % sign within a like expression to do a wildcard match against zero or more characters. Using wildcards allows you to have some fuzziness in your search string.

In the example below, we are looking for documents of type “landmark” where the name property matches any string that begins with “eng” followed by zero or more characters, the letter “e”, followed by zero or more characters.
The following query will return “landmark” type documents with name matching “Engineers”, “engine”, “english egg” , “England Eagle” and so on. Notice that the matches may span word boundaries.

Wildcard Character Match

We can use "_" sign within a like expression to do a wildcard match against a single character.

In the example below, we are looking for documents of type “landmark” where the name property matches any string that begins with “eng” followed by exactly 4 wildcard characters and ending in the letter “r”.
The following query will return “landmark” type documents with name matching “Engineer”, “engineer” and so on.

Regex Match

The regex expression can be used for case sensitive matches. Similar to wildcard like expressions, regex expressions based pattern matching allow you to have some fuzziness in your search string.

In the example below, we are looking for documents of type “landmark” where the name property matches any string (on word boundaries) that begins with “eng” followed by exactly 4 wildcard characters and ending in the letter “r”.
The following query will return “landmark” type documents with name matching “Engine”, “engine” and so on. Note that the \b specifies that the match must occur on word boundaries.

Documents that have Null Or Missing Property

One of the features of the query language that sets it apart from SQL is the ability to query for documents which have null or missing properties.

The isNullOrMissing() expression is used in conjunction with the Property Expression to test if the specified property has a null or missing value. The notNullOrMissing() does the reverse.

In the example below, we are looking for all documents in which the email property that is null or missing.

Ordering of Documents

It is possible to sort the results of a query based on a given expression result.

The example below returns documents of type equal to “hotel” sorted in ascending order by the value of the title property.

String Manipulation

String manipulation capabilities are integral to any data processing. Couchbase Lite supports the following string processing functions –

The example below returns documents of where the email property contains the substring “natgeo.org”. The name property value is converted to uppercase in the response.

Collation

Collation functions enhance string comparison and sorting capabilities by supporting unicode strings, locale-aware string manipulation and language specific features like diacritics. You can learn about collation in this writeup by Jens Alfke.

The example below defines the Collation rules that the case and accents be ignored. This collator is applied to the string comparison function on the name property. The results will include documents where name is equal to strings strings like “Hotel Novotel Paris La Defense” , “Hotel Novotel Paris La Défense” and so on.

There are some limitations in the Collation support – at the time of writing this post, it cannot be used with pattern matching queries such as like and regex .

What Next

This blog post gave you a glimpse into the powerful new Query Interface supported in Couchbase Lite 2.0. You can download the Developer Preview builds from our downloads page. In upcoming posts, we will examine advanced queries including Collections, Joins and Full Text Search. So stay tuned!

If you have questions or feedback, please leave a comment below or feel free to reach out to me at Twitter @rajagp or email me priya.rajagopal@couchbase.com.  The Couchbase Forums are another good place to reach out with questions.

Everything discussed in this post is based on Couchbase Mobile 2.0 Developer Preview–019. So there may be enhancements to the API before we go GA. We will repost with updates.

 

Posted by Priya Rajagopal, Developer Advocate, Couchbase

Priya Rajagopal is a Mobile Developer Advocate for Couchbase, living in Ann Arbor, MI. She has been professionally developing software for over 18 years and was most recently the Director of Mobile Development at a startup. Although her current interests lie in mobile development, she has previously worked on a range of technologies including IPTV, Social TV, targeted advertising, network management , RESTful architectures and platform security. As a TISPAN IPTV standards delegate, she was a key contributor to the IPTV architectural specifications. She has spent a decade in software R&D and is a co-inventor on almost 2 dozen US patents.

Leave a reply