One of the major features introduced in Couchbase Lite 2.0 , now in Developer Preview is the new Query interface 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. We covered the basics of the interface in an earlier blog post. Arrays are an integral component of modeling data with JSON. In this blog post, we will discuss querying of array collections using the new API.

This is the second in a series of posts that discusses the query interface. This blog assumes you are familiar with the fundamentals, so if you haven’t done so already, be sure to review the earlier post first. Stay tuned for future blog posts on advanced querying capabilities including Full Text Search.

Note: Everything discussed in this post is based on Couchbase Mobile 2.0 Developer Preview–021. 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 with 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 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 query 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

  • 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” . The JSON document model is shown below. For brevity, we have omitted some of the properties from the model below.

Specifically, note that the model includes nested collections – public_likes and reviews. The queries in following sections will be dealing with these nested collections.

Refer to the model above for each of the query examples below.

The Database Handle

In 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

Array Containment

The query below fetches the ids, names and public_likes properties of documents where the public_likes array property in “hotel” type documents contains the value of “Corrine Hilll”. For this, the ArrayFunction.contains function expression is used on the public_likes array.

Array Size

The query below fetches the ids, names properties and the size of public_likes array property in “hotel” type documents. For this, the ArrayFunction.length function expression is used on the public_likes array to get the size of the array.

Also, notice that we are using as expression to alias the array count value to NumLikes. We had introduced aliases in the earlier blog post on Query Fundamentals. If you do not alias the result of the arrayLength expression, the property key would be $1 , which is not very intuitive.

Evaluating Array Members

While the ArrayFunction.contains function expression allows you to check if the given array contains a specific value, the in array expression can be used to evaluate any or all of the memebers of an array against a criteria specified by the satisfies expression. This is a powerful document filtering capability.

The in expression is used with the any, every or the anyAndEvery quantified operators on ArrayExpression to evaluate any, every or any/every element in the array object.

The following query returns the documents where any of the values in the public_likes array begins with the characters “Corr”.

  1. Declare a variable with name “likedby” to represent every element in the public_likes array
  2. The any ArrayExpression checks if the array element represented by the likedby variable satisfies the criteria in the like expression. The like expression checks if the value of the item represented by the “likedby” variable begins with “Corr”.

Evaluating Nested Arrays

You can evaluate the members a nested array. For this, you can apply a keypath to the variable expression. The nested array has to be one level deep.

Example 1

The following query returns the documents where any of the values in the reviews array has the author property that begins with “Ozella”. From the data model above you can see that the “reviews” property holds an array of objects. The objects contain an author property.

  1. Declare a variable with name “review” to represent every element in the reviews array
  2. The any expression checks if the array element represented by the review variable satisfies the criteria in the like ArrayExpression. The like expression checks if the value of author property of the item represented by the “review” variable begins with “Ozella”.

Example 2

The following query returns the documents where any of the values in the nested ratings array has the Overall property rating that is greater than or equal to 4.
As you may have noted from the data model above, the “reviews” property holds an array of objects. The objects contain a nested ratings array which holds the Overall property.

  1. Declare a variable with name “review” to represent every element in the reviews array
  2. The any expression checks if the array element represented by the review variable satisfies the criteria in the comparison expression. The comparison expression checks the value of Overall property of the ratings array in the object represented by the “review” variable is greater than or equal to 4.

Limitations

The array manipulation capabilities are not nearly as extensive as N1QL’s feature set. But it’s a good starting point. These capabilities may be available in future releases of Couchbase Mobile.

So for now, it’s upto the app to manipulate the array results using the language’s collection processing capabilities.
Let’s consider this example in swift
  • Referring to the data model, let’s say you wanted to determine the minimum Cleanliness rating for a given hotel based on the reviews on the hotel.

From the model above , note that the Cleanliness property is a member of the the ratings property contained in each object that is member of reviews array.

For this, you can do a Couchbase Lite query to fetch the reviews array property for a hotel with specified Id as follows –

The resultSet response to the above query would be an array with a single element. This element would correspond to the “hotel” document for the specified Id.

Now, the app has to implement the logic to iterate over the reviews array and for each member of the array, to fetch the ratings property and corresponding Cleanliness value.

Here is one possible way to do it in swift.

  • First, iterate over the resultSet and extract the value of “reviews” property.

After the loop processing, the “matches” array would be something like the one below. It would be an array containing the nested array corresponding to the reviews –

  • You can then use swift language features like flatMap and map to process the resulting array to derive the minimum “Cleanliness” rating for given hotel

You would do something similar in languages that support functional constructs like flatmap and map.

What Next

This blog post looked at how you can handle Array collection types using the new Query API in Couchbase Mobile 2.0. This is a start. Expect to see more functionality in future releases. You can download the Developer Preview builds from our downloads page. In upcoming posts, we will examine advanced queries including 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–021. 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