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
|
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 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
|
1 2 3 4 5 6 7 |
{ "type": "employee", "firstname": "John", "lastname": "Smith", "department": "1000", "location": "101" } |
- “department” type document
|
1 2 3 4 5 6 7 8 9 10 11 |
{ "type": "department", "name": "Product Management", "code": "2000", "head": { "firstname": "Patricia", "lastname": "Shoji" }, "location":["101","102"] } |
- “location” type document
|
1 2 3 4 5 6 |
{ "type": "location", "name": "HQ", "address": "1123 6th St. Melbourne, FL 32904 ", "code": "101" } |
** 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 |
let 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") |
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
|
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 |
try db.createIndex(IndexBuilder.valueIndex(items: ValueIndexItem.property("type")),withName: "typeIndex") // set up aliases to represent the data source for "department" type document let departmentDS = DataSource.database(db).as("departmentDS") // Property expression for "department" (in employee documents) let employeeDeptExpr = Expression.property("department").from("employeeDS") // Property expression for "code" (in department documents) let departmentCodeExpr = Expression.property("code").from("departmentDS") // Join clause: Join employee type and department type documents where the // "department" field of employee documents is equal to the department "code"; // of "department" documents let joinExpr = employeeDeptExpr.equalTo(departmentCodeExpr) .and(Expression.property("type").from("employeeDS").equalTo(Expression.string("employee"))) .and(Expression.property("type").from("departmentDS").equalTo(Expression.string("department"))) // Construct inner join expression with ON query. let join = Join.join(departmentDS).on(joinExpr) // return the "firstname", "lastname"; and "department"; name from the documents that are joined based // on the JOIN expression let searchQuery = Query.select( SelectResult.expression(Expression.property("firstname").from("employeeDS")), SelectResult.expression(Expression.property("lastname").from("employeeDS")), SelectResult.expression(Expression.property("name").from("departmentDS"))) .from(employeeDS) .join(join) |
ANSI SQL
The equivalent SQL statement for the above query would be
|
1 2 3 4 5 6 7 8 9 10 |
SELECT employeeDS.firstname, employeeDS.lastname, departmentDS.name FROM `travel-sample` employeeDS INNER JOIN `travel-sample` departmentDS ON employeeDS.department = departmentDS.code WHERE employeeDS.type = "employee" AND departmentDS.type = "department" |
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
|
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 |
// set up aliases to represent the data source for "employee" type document let employeeDS = DataSource.database(db).as("employeeDS") // set up aliases to represent the data source for "department" type document let departmentDS = DataSource.database(db).as("departmentDS") // Property expression for "department" (in employee documents) let employeeDeptExpr = Expression.property("department").from("employeeDS") // Property expression for "code" (in department documents) let departmentCodeExpr = Expression.property("code").from("departmentDS") // Join clause: Join department type and employee type documents where the // "department" field of employee documents is equal to the department "code" of // "department" documents let joinExpr = employeeDeptExpr.equalTo(departmentCodeExpr) .and(Expression.property("type").from("employeeDS").equalTo(Expression.string("employee"))) .and(Expression.property("type").from("departmentDS").equalTo(Expression.string("department"))) // join expression with ON query let join = Join.leftJoin(departmentDS).on(joinExpr) // return the "firstname", "lastname" and "department" name from the documents that are joined based on the JOIN expression let searchQuery = Query.select( SelectResult.expression(Expression.property("firstname").from("employeeDS")), SelectResult.expression(Expression.property("lastname").from("employeeDS")), SelectResult.expression(Expression.property("name").from("departmentDS"))) .from(employeeDS) .join(join) |
ANSI SQL
The equivalent SQL statement for the above query would be
|
1 2 3 4 5 6 7 8 9 10 |
SELECT employeeDS.firstname, employeeDS.lastname, departmentDS.name FROM `travel-sample` employeeDS LEFT JOIN `travel-sample` departmentDS ON employeeDS.department = departmentDS.code WHERE employeeDS.type = "employee" AND departmentDS.type = "department" |
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
|
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 |
// set up aliases to represent the data source for "department" type document let departmentDS = DataSource.database(db).as("departmentDS") // set up aliases to represent the data source for "location" type document let locationDS = DataSource.database(db).as("locationDS") // Property expression for "department" (in employee documents) let employeeDeptExpr = Expression.property("code").from("departmentDS") // Property expression for "department" (in location documents) let departmentCodeExpr = Expression.property("code").from("locationDS") // cross join expression let join = Join.crossJoin(locationDS) // type property expressions let deptTypeExpr = Expression.property("type").from("departmentDS") let locationTypeExpr = Expression.property("type").from("locationDS") // The where clauses filters the set of documents to cross-join on // We alias the "code" properties since it exists in both the department and location docs // NOTE: The where clause is used to filter the documents to be considered as // part of the cartesian join let searchQuery = Query.select( SelectResult.expression(Expression.property("name").from("departmentDS")).as("DeptName"), SelectResult.expression(Expression.property("name").from("locationDS")).as("LocationName"), SelectResult.expression(Expression.property("address").from("locationDS"))) .from(departmentDS) .join(join) .where(deptTypeExpr.equalTo(Expression.string("department")) .and(locationTypeExpr.equalTo(Expression.string("location")))) |
ANSI SQL
The equivalent SQL statement for the above query would be
|
1 2 3 4 5 6 7 8 9 |
SELECT departmentDS.name AS DeptName, locationDS.name AS LocationName, locationDS.address FROM `travel-sample` departmentDS CROSS JOIN `travel-sample` locationDS WHERE departmentDS.type = "department" |
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
|
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 43 44 |
// set up aliases to represent the data source for "employee" type document let employeeDS = DataSource.database(db).as("employeeDS") // set up aliases to represent the data source for "department" type document let departmentDS = DataSource.database(db).as("departmentDS") // set up aliases to represent the data source for "location" type document let locationDS = DataSource.database(db).as("locationDS") // Property expression for "department" property (in employee documents) let employeeDeptExpr = Expression.property("department").from("employeeDS") // Property expression for "location" property (in employee documents) let employeeLocationExpr = Expression.property("location").from("employeeDS") // Property expression for "code" property (in department documents) let departmentCodeExpr = Expression.property("code").from("departmentDS") // Property expression for "code" property (in location documents) let locationCodeExpr = Expression.property("code").from("locationDS") // Join Criteria 1 // Join where the "department" field of employee documents is equal to the department "code" of "department" documents let joinDeptCodeExpr = employeeDeptExpr.equalTo(departmentCodeExpr) .and(Expression.property("type").from("employeeDS").equalTo(Expression.string("employee"))) .and(Expression.property("type").from("departmentDS").equalTo(Expression.string("department"))) // Join Criteria 2 // Join where the "department" field of employee documents is equal to the department "code" of "department" documents let joinLocationCodeExpr = employeeLocationExpr.equalTo(locationCodeExpr) .and(Expression.property("type").from("employeeDS").equalTo(Expression.string("employee"))) .and(Expression.property("type").from("locationDS").equalTo(Expression.string("location"))) // join expression for department code let joinDeptCode = Join.join(departmentDS).on(joinDeptCodeExpr) // join expression for location code let joinLocationCode = Join.join(locationDS).on(joinLocationCodeExpr) // Multiple join expressions in the join clause let searchQuery = QueryBuilder.select(SelectResult.expression(Expression.property("firstname").from("employeeDS")), SelectResult.expression(Expression.property("lastname").from("employeeDS")), SelectResult.expression(Expression.property("name").from("departmentDS")).as("deptName"), SelectResult.expression(Expression.property("name").from("locationDS")).as("locationName")) .from(employeeDS) .join(joinDeptCode,joinLocationCode) |
ANSI SQL
The equivalent SQL statement for the above query would be
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT employeeDS.firstname, employeeDS.lastname, departmentDS.name AS deptName, locationDS.name AS locationName FROM `travel-sample` employeeDS JOIN `travel-sample` departmentDS ON employeeDS.department = departmentDS.code JOIN `travel-sample` locationDS ON employeeDS.location = locationDS.code WHERE departmentDS.type = "department" AND locationDS.type = "location" AND employeeDS.type = "employee" |
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.
|
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 |
// set up aliases to represent the data source for "department" type document let departmentDS = DataSource.database(db).as("departmentDS") // set up aliases to represent the data source for "location" type document let locationDS = DataSource.database(db).as("locationDS") // Property expression for "location" property (in department documents) let departmentLocationExpr = Expression.property("location").from("departmentDS") // Property expression for "code" property (in location documents) let locationCodeExpr = Expression.property("code").from("locationDS") // Join where the "code" field of location documents is contained in // the "location" array of "department" documents let joinDeptCodeExpr = ArrayFunction.contains(departmentLocationExpr, value: locationCodeExpr) .and(Expression.property("type").from("locationDS").equalTo(Expression.string("location")) .and(Expression.property("type").from("departmentDS").equalTo(Expression.string("department")))) // join expression let joinLocationCode = Join.join(departmentDS).on(joinDeptCodeExpr) // Search query with JOIN let searchQuery = QueryBuilder.select( SelectResult.expression(Expression.property("name").from("departmentDS")).as("departmentName"), SelectResult.expression(Expression.property("name").from("locationDS")).as("locationName")) .from(locationDS) .join(joinLocationCode) |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT departmentDS.name AS departmentName, locationDS.name AS locationName FROM `travel-sample` locationDS JOIN `travel-sample` departmentDS ON ANY code IN departmentDS.location SATISFIES code = locationDS.location END WHERE departmentDS.type = "department" AND locationDS.type = "location" |
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.
