Introducing the Query Interface in Couchbase Mobile 2.0

The Couchbase Lite 2.0 Release, brings a plethora of new features and enhancements. We discussed one such enhancement, namely the new replication protocol between 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 post introduces you to the query interface and covers the basics. Refer to the last section of this post for specifics on other related blog posts on more advanced query capabilities.

You can download the Couchbase Mobile 2.0 pre-release builds from our downloads page.

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, a simple interface allows you to create indexes and you can use a Query Builder interface to construct your queries. 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 differeces, 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 if you are interested in a sample Swift Project

• Clone the iOS Swift Playground from GitHub
  
  $ git clone https://github.com/couchbaselabs/couchbase-lite-ios-api-playground

  1	$ git clone https://github.com/couchbaselabs/couchbase-lite-ios-api-playground

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

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 type and name 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 )
  
  [ { "travel-sample": { "callsign": "MILE-AIR", "country": "United States", "iata": "Q5", "icao": "MLA", "id": 10, "name": "40-Mile Air", "type": "airline" } }, { "travel-sample": { "callsign": "TXW", "country": "United States", "iata": "TQ", "icao": "TXW", "id": 10123, "name": "Texas Wings", "type": "airline" } } ]

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  [     {         "travel-sample": {             "callsign": "MILE-AIR",             "country": "United States",             "iata": "Q5",             "icao": "MLA",             "id": 10,             "name": "40-Mile Air",             "type": "airline"         }     },     {         "travel-sample": {             "callsign": "TXW",             "country": "United States",             "iata": "TQ",             "icao": "TXW",             "id": 10123,             "name": "Texas Wings",             "type": "airline"         }     } ]

  
  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
  
  Swift

  for row in try searchQuery.execute() { if let dict = row.toDictionary() as? [String:Any], let docObject = dict["travel-sample"] as? [String:Any] { // You can now process the document properties let name = docObject["name"] let type = docObject["type"] } }

  1 2 3 4 5 6 7 8

  for row in try searchQuery.execute() {         if let dict = row.toDictionary() as? [String:Any],             let docObject = dict["travel-sample"] as? [String:Any] {             // You can now process the document properties             let name = docObject["name"]             let type = docObject["type"]         }     }

  
   

• 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 –
  
  [ { "id": "airline_112" }, { "id": "airline_189" }, { "id": "airline_1209" } ]

  1 2 3 4 5 6 7 8 9 10 11

  [         {             "id": "airline_112"         },         {             "id": "airline_189"         },         {             "id": "airline_1209"         }     ]

  
  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
  
  Swift

  for row in try searchQuery.execute() { if let dict = row.toDictionary() as? [String:Any], let docId = dict["id"] as? String { // You can now fetch the details of the document using the Id let doc = try db.getDocument(docId) } }

  1 2 3 4 5 6 7

  for row in try searchQuery.execute() {         if let dict = row.toDictionary() as? [String:Any],             let docId  = dict["id"] as? String {             // You can now fetch the details of the document using the Id             let doc = try db.getDocument(docId)         }         }

• 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 –
  
  [ { "type": "airline" }, { "type": "airline" }, { "type": "hotel" } ]

  1 2 3 4 5 6 7 8 9 10 11

  [         {             "type": "airline"         },         {             "type": "airline"         },         {             "type": "hotel"         }     ]

  
  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
  
  Swift

  for row in try searchQuery.execute() { if let docObject = row.toDictionary() as? [String:Any] { // You can now fetch the details of the document using the property name as key let type = docObject["type"] } }

  1 2 3 4 5 6

  for row in try searchQuery.execute() {    if let docObject = row.toDictionary() as? [String:Any]  {       // You can now fetch the details of the document using the property name as key          let type = docObject["type"]         }         }

  
  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 earlierFor instance,consider the query
  
  Swift

  let searchQuery = QueryBuilder .select(SelectResult.expression(Meta.id), SelectResult.expression(Expression.property("type"))) .from(DataSource.database(db)) .limit(Expression.int(limit))

  1 2 3 4 5

  let searchQuery = QueryBuilder             .select(SelectResult.expression(Meta.id),                     SelectResult.expression(Expression.property("type")))             .from(DataSource.database(db))             .limit(Expression.int(limit))

  
  The results would look something like this –
  
  [ { "id":"airline_1001", "type": "airline" }, { "id":"airline_900", "type": "airline" }, { "id":"hotel_1001", "type": "hotel" } ]

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  [         {             "id":"airline_1001",             "type": "airline"           },         {             "id":"airline_900",             "type": "airline"         },         {             "id":"hotel_1001",             "type": "hotel"         }     ]

  
  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
  
  Swift

  for row in try searchQuery.execute() { if let docObject = row.toDictionary() as? [String:Any] { // You can now fetch the details of the document using the property name as key let type = docObject["type"] let docId = docObject["id"] } }

  1 2 3 4 5 6 7

  for row in try searchQuery.execute() {     if let docObject = row.toDictionary() as? [String:Any]  {     // You can now fetch the details of the document using the property name as key         let type = docObject["type"]         let docId = docObject["id"]      }     }

  
   

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
* lessThan
* notLessThan
* lessThanOrEqualTo
* notLessThanOrEqualTo
* greaterThan
* notGreaterThan
* greaterThanOrEqualTo
* notGreaterThanOrEqualTo
* 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. These perform **case sensitive ** matches. So if you want to make the string matching case insensitive, you would have to use Function.lower or Function.upper to transform the matched string to lowercase or uppercase equivalents.

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 sensitive match, we used Function.lower to transform matched string to lowercase equivalent. So 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. Once again, we are using Function.lower to make the search case insenstive.

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

Notice that unlike the previous examples, we are not using Function.lower to transform search string to lowercase. So the search will be case senstive.
The following query will return “landmark” type documents with name matching “Engineer”, “Engineer1” 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”. Once again, we are using Function.lower to make the search case insenstive.

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 isNullOrMissing() 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. In previous examples, we looked at how the Function.lower could be used to transform a string to lowercase equivalent to make case insenstive string comparisons.

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 .

Parameterization

One of the more powerful features of the query interface is the ability to set parameters on a query. This gives you the flexibility to update the parameter values to a query at any point without having to re-create or re-start the query. Changing the query parameters will auto re-start the query and the new parameters will take effect.

Referring to the data model above, lets suppose that you want to fetch documents where the number public-likes is within  a certain range. In the example below, we are looking for “hotel” type documents where the umber of public-likes is between 5 and 10.

Note that the example above, you may have noticed the use of ArrayFunction. Couchbase Lite 2.0 provides us with extensive array manipulation support. This will be discussed future in this blog post on collections.

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 Couchbase Mobile 2.0 pre-release builds from our downloads page.

Here are other Couchbase Mobile Query related posts that may be of interest
– This blog post discusses the Full Text Search capabilities.
– This blog post discusses how to query array collections
– This blog post discusses how to do JOIN queries

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.