Couchbase Lite 2.0 supports the ability to perform JOINS across your JSON documents . This is part of 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.

JOINS enable you to combine the contents of multiple documents. In this post, we will provide examples to illustrate the types of JOINS possible with Couchbase Lite. For each of the queries, we will provide the equivalent SQL query. This blog assumes you are familiar with the fundamentals of the new query API, so if you haven’t done so already, be sure to review the earlier post first. If you are interested, links to blogs discussing other features of the Query interface are provided at the end of this post.

You can download the  latest 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, 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 a sample database located here. You can embed this pre-built database into your mobile application and start using it for your queries.

The sample data set is a bit contrived but the goal here is to demonstrate some typical use cases of join queries.

  • “employee” type document

  • “department” type document

  • “location” type document

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

JOIN or Inner JOIN

You can use a simple JOIN or Inner JOIN Query to fetch properties from participating documents if and only if both documents meet the conditions specified in the ON clause.

For example, considering the data model we presented earlier, let’s suppose that you wanted to fetch the firstName, lastName of an “employee” and the corresponding name of the “department” that the employee belonged to. In this case, firstname and lastname properties are fetched from document of type “employee” and the department name is fetched from the document of type “department” if and only if the department property of the “employee” matches the corresponding code property in the “department”

This implies that if there are no “department” documents that match the code in the “employee” document then the details of that employee are not included in the output result

Request

 

ANSI SQL

The equivalent SQL statement for the above query would be

 

Left JOIN or Left Outer JOIN

You can use a left JOIN Query to fetch properties from participating documents if both documents meet the conditions specified in the ON clause. However, unlike a regular JOIN, the results will also include unmatched documents to the left-hand-side of the ON clause of the JOIN Expression.

For example, considering the data model we presented earlier, let’s suppose that you wanted to fetch the firstName, lastName of an “employee” and the corresponding name of the “department” that the employee belonged to.
In addition, let’s suppose that we are also interested in thefirstName and lastName of an “employee” whose department code does not correspond to a valid department. This could be the case for instance, if the department code for the employee was entered incorrectly.

In this case, firstname and lastname properties are fetched from document of type “employee” and the department name is fetched from the document of type “department” if the department property of the “employee” matches the corresponding code property in the “department”.

If there is no matching department, then only the firstname and lastname properties from the “employee” document are returned.

Request

ANSI SQL

The equivalent SQL statement for the above query would be

 

Cross JOIN

You can use a cross JOIN Query to fetch the cartesian product of the properties from participating documents.The documents are typically not related to each other. This is the equivalent of a inner JOIN without the ON clause of the join expression.

For example, considering the data model we presented earlier, let’s suppose that you wanted to fetch the cartesian product of all documents of type “location” and type “department”. In other words, every “location” type document would be combined with each of the “department” type documents.

Since there is no on clause specified in the cross JOIN expression, you would need to include a where clause to filter the subset of documents to be considered based on document type.

Request

ANSI SQL

The equivalent SQL statement for the above query would be

 

Chaining of JOINs

It is possible to specify multiple JOIN expressions in your select clause to be able to JOIN across documents based on different criteria.

For example, considering the data model we presented earlier, let’s suppose that you wanted to fetch the firstName, lastName of an “employee” and the corresponding name of the “department” that the employee belonged to. In addition, you also wanted to identify the name of the “location” that the employee was based in.

In this case, we use two JOIN expressions.
The first JOIN expression is used to JOIN documents of type “employee” with documents of type “department” based on the “department code” property. In this case, the firstname and lastname properties are fetched from document of type “employee” and the department name is fetched from the document of type “department” if and only if the department property of the “employee” matches the corresponding code property in the “department”.

The second JOIN expression is used to JOIN documents of type “employee” with documents of type “location” based on the “location code” property. In this case, the firstname and lastname properties are fetched from document of type “employee” and the location name is fetched from the document of type “location” if and only if the location property of the “employee” matches the corresponding code property in the “department”.

Request

ANSI SQL

The equivalent SQL statement for the above query would be

 

JOIN Expressions with Functions

While all the examples used the equalTo comparison in the JOIN expression, it should be noted that you could use any comparison operators such as between, greaterThanOrEqualTo and so on in the JOIN expression. You can also include any valid Function expressions. This is a powerful feature.

For example, considering the data model we presented earlier, let’s suppose that you wanted to fetch the department name and corresponding location names of the “location” where the department was based. A department could belong to one or more locations.
In this case, the JOIN expression would join “department” and “location” type documents by looking for matches in any of the members of the location array property of the department document using the ArrayFunction expression.

 

ANSI SQL

Arrays are not supported in SQL. However N1QL includes support for arrays. The corresponding SQL-like statement for the above query would be

 

What Next

This blog post reviewed the powerful JOIN feature in Couchbase Mobile 2.0 that allows you to combine results from multiple JSON documents. You can download Couchbase Mobile 2.0 and test out the queries discussed in this post. This is a start. Expect to see more functionality in future releases.

Here are a few other Couchbase Mobile Query related posts that may be of interest
– This blog post discusses the fundamentals
– This blog post discusses how to query array collections
– This blog post discusses Full Text Search (FTS) capabilities.

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.

Posted by Priya Rajagopal , Principal Product Manager

Priya Rajagopal is a Principal Product Manager for Couchbase focusing on the Couchbase Mobile platform. She has been professionally developing software for over 19 years in several technical leadership positions with the last 7 years on mobile technologies. She has spent a decade in software R&D and is a co-inventor on 22 technology related patents.

Leave a reply