One of the major features introduced in Couchbase Lite 2.0, 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 blog assumes you are familiar with the fundamentals, so if you haven’t done so already, be sure to review the earlier post first. The last section of the post lists links to other relevant query blogs.
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
|
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” . 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.
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
{ "type": "hotel", "name": "Medway Youth Hostel", "address": "Capstone Road, ME7 3JE", "city": "Medway", "country": "United Kingdom", "description": "blah blah", "public_likes": [ "Julius Tromp I", "Corrine Hilll" ], "reviews": [ { "author": "Ozella Sipes", "content": "blah blah.", "date": "2013–06–22 18:33:50 +0300", "ratings": { "Cleanliness": 5, "Location": 4, "Overall": 4, "Rooms": 3, "Service": 5, "Value": 4 } }, { "author": "Jeremy Snapes", "content": "blah blah.", "date": "2013–05–05 18:33:50 +0300", "ratings": { "Cleanliness": 2, "Location": 2, "Overall": 4, "Rooms": 3, "Service": 5, "Value": 4 } } ], "url":"http://www.yha.org.uk", "vacancy": true } |
** 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.
|
1 2 |
var options = DatabaseConfiguration() let db = try Database(name: kDBName, config: options) |
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
|
1 |
try db.createIndex(IndexBuilder.valueIndex(items: ValueIndexItem.property("type")),withName: "typeIndex") |
The following example creates a ValueIndex on type and name properties of a Document
|
1 |
try db.createIndex(IndexBuilder.valueIndex(items: ValueIndexItem.property("type"),ValueIndexItem.property("name")),withName: "TypeNameIndex") |
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.
|
1 2 3 4 5 6 |
let searchQuery = QueryBuilde.select(SelectResult.expression(Meta.id), SelectResult.expression(Expression.property("name")), SelectResult.expression(Expression.property("public_likes"))) .from(DataSource.database(db)) .where(Expression.property("type").equalTo(Expression.string("hotel")) .and( ArrayFunction.contains(Expression.property("public_likes"), value: Expression.string("Corrine Hilll")))) |
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.
|
1 2 3 4 5 6 |
let searchQuery = QueryBuilder.select(SelectResult.expression(Meta.id), SelectResult.expression(Expression.property("name")), SelectResult.expression(ArrayFunction.length(Expression.property("public_likes"))).as("NumLikes")) .from(DataSource.database(db)) .where(Expression.property("type").equalTo(Expression.string("hotel"))) .limit(Expression.int(limit)) |
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.
Theinexpression 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 2 3 4 5 6 7 8 9 10 11 |
//1. let VAR_LIKEDBY = ArrayExpression.variable("likedby") //2. let searchQuery = QueryBuilder.select(SelectResult.expression(Meta.id), SelectResult.expression((Expression.property("public_likes")))) .from(DataSource.database(db)) .where(Expression.property("type").equalTo(Expression.string("hotel")) .and(ArrayExpression.any(VAR_LIKEDBY).in(Expression.property("public_likes")) .satisfies(VAR_LIKEDBY.like(Expression.string("Cor%"))))) .limit(Expression.int(limit))<code> |
-
- Declare a variable with name “likedby” to represent every element in the
public_likesarray - The
anyArrayExpression checks if the array element represented by thelikedbyvariable satisfies the criteria in thelikeexpression. Thelikeexpression checks if the value of the item represented by the “likedby” variable begins with “Cor”.
- Declare a variable with name “likedby” to represent every element in the
Indexing Arrays
You can also query elements at specific indexes. The following query returns the name and first member of the public_likes array properties of all “hotel” documents
|
1 2 3 4 5 6 |
let searchQuery = QueryBuilder.select(SelectResult.expression(Meta.id), SelectResult.expression(Expression.property("name")), SelectResult.expression(Expression.property("public_likes[0]"))) .from(DataSource.database(db)) .where(Expression.property("type").equalTo(Expression.string("hotel"))) .limit(Expression.int(limit)) |
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.
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. Each of the objects contain a nested ratings array which in turn contains the Overall property.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
// 1. let VAR_OVERALL = ArrayExpression.variable("review.ratings.Overall") //2. let VAR_REVIEWS = ArrayExpression.variable("review") //3. let searchQuery = QueryBuilder.select(SelectResult.expression(Meta.id), SelectResult.expression(Expression.property("name"))) .from(DataSource.database(db)) .where(Expression.property("type").equalTo(Expression.string("hotel")) .and(ArrayExpression.any(VAR_REVIEWS).in(Expression.property("reviews")) .satisfies(VAR_OVERALL.greaterThanOrEqualTo(Expression.int(4))))) .limit(Expression.int(limit)) |
- Declare a variable to represent an element in the
review.ratings.Overallarray - Declare a variable to represent every element in the
reviewsarray - The
anyexpression checks if the array element represented by thereviewvariable satisfies the criteria in thecomparisonexpression. Thecomparisonexpression checks the value ofOverallproperty of theratingsarray 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
Cleanlinessrating 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 –
|
1 2 3 4 5 6 |
// 1. Query for reviews property array for the given hotel let searchQuery = QueryBuilder.select( SelectResult.expression(Expression.property("reviews"))) .from(DataSource.database(db)) .where(Expression.property("type").equalTo(Expression.string("hotel")) .and(Meta.id.equalTo(Expression.string("hotel_10025")))) |
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.
|
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 26 27 28 29 30 31 32 |
[ { "reviews": [ { "author": "Ozella Sipes", "content": "blah", "date": "2013–06–22 18:33:50 +0300", "ratings": { "Cleanliness": 5, "Location": 4, "Overall": 4, "Rooms": 3, "Service": 5, "Value": 4 } }, { "author": "fuzzy Snipes", "content": "blah", "date": "2013–06–22 18:33:50 +0300", "ratings": { "Cleanliness": 2, "Location": 3, "Overall": 4, "Rooms": 3, "Service": 5, "Value": 4 } } ] } ] |
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.
|
1 2 3 4 5 6 7 8 |
var matches:[[String:Any]] = [[String:Any]]() do { for row in try searchQuery.execute() { if let reviewData = row.array(forKey: "reviews")?.toArray() as? [[String:Any]] { matches.append(reviewData) } } } |
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 –
|
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 26 27 28 29 30 31 |
[ [ { "author": "Ozella Sipes", "content": "blah", "date": "2013–06–22 18:33:50 +0300", "ratings": { "Cleanliness": 5, "Location": 4, "Overall": 4, "Rooms": 3, "Service": 5, "Value": 4 } }, { "author": "fuzzy Snipes", "content": "blah", "date": "2013–06–22 18:33:50 +0300", "ratings": { "Cleanliness": 2, "Location": 3, "Overall": 4, "Rooms": 3, "Service": 5, "Value": 4 } } ] ] |
- You can then use swift language features like
flatMapandmapto process the resulting array to derive the minimum “Cleanliness” rating for given hotel
|
1 2 3 4 5 6 |
let minCleanlinessValue = matches.flatMap{$0} .map{return ($0["ratings"] as? [String:Any])?["Cleanliness"] as? Int} .flatMap{$0} .min { (a, b) -> Bool in return a < b } |
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 Pre-release build from our downloads page.
Here are other Couchbase Mobile Query related posts that may be of interest
– This blog post discusses the fundamentals of the Query API
– This blog post discusses the Full Text Search capabilities.
– 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.
