How to Query Array Collections in Couchbase Lite

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.

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

Sample Data Model

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

varoptions=DatabaseConfiguration()

let db=tryDatabase(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

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.

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

Declare a variable with name “likedby” to represent every element in the public_likes array

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

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

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.

Declare a variable to represent an element in the review.ratings.Overall array

Declare a variable to represent every element in the reviews array

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 minimumCleanliness 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 –

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 flatMap and map to process the resulting array to derive the minimum “Cleanliness” rating for given hotel

Swift

1

2

3

4

5

6

letminCleanlinessValue=matches.flatMap{$0}

.map{return($0["ratings"]as?[String:Any])?["Cleanliness"]as?Int}

.flatMap{$0}

.min{(a,b)->Boolin

returna<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

Posted by Priya Rajagopal , Principal Product Manager

Priya Rajagopal is a Principal Product Manager for Couchbase focusing on the Couchbase Mobile and IoT platform. She has been professionally developing software for over 19 years in several technical leadership positions, with the last 8 years focussed on mobile technologies. She is a co-inventor on 22 US patents and holds a masters degree in Computer Science.