Working with JSONStore Collections (Join)
Nana Amfo May 01, 2016
JSONStore MobileFirst_Platform
Overview
Currently, JSONStore does not have a dedicated feature to join collection like most relational databases. However, you can utilize shared indexes using additional search fields to relate different collections. In this blog, I will demonstrate how this is done and at the end of the blog I will attach an app that you can use to help you. In addition, I would recommend using the latest publicly available version of MobileFirst Platform but this may apply to older versions as well.
Create the collections
Assuming you know the basics of creating collections in JSONStore, you will know that you can create a collection with search fields that are not part of the collection.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
var OrdersCollection = {
orders: {
searchFields: {
order_id: 'integer',
order_date: 'string'
},
additionalSearchFields: {
customer_id: 'integer'
}
}
};
var CustomerCollection = {
customers: {
searchFields: {
customer_name : 'string',
contact_name : 'string',
country : 'string'
},
additionalSearchFields : {
customer_id : 'integer'
}
}
};
As you see from the example above, the additional search field is the same for both the customer collection and the orders collection. You can call this additional search field a shared index.
Adding data using additional search fields
Since additional search fields are not part of the data you will need to specify it when adding each new data to your collection.
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
var data = [
{order_id : 462, order_date : '1-1-2000'},
{order_id: 608, order_date : '2-2-2001'},
{order_id: 898, order_date : '3-3-2002'}
];
var counter = 0;
var q = async.queue(function (task, callback) {
setTimeout(function () {
WL.JSONStore.get('orders').add(task.data, {additionalSearchFields: {customer_id: task.customer_id}});
callback(++counter);
},0);
},1);
for(var i = 0; i < data.length; i++){
q.push({data : data[i], customer_id: i+1}, function(counter){
console.log("Added Order Doc " + counter);
});
}
q.drain = function(){
setTimeout(function() {
console.log("Finished adding order documents");
WL.JSONStore.get("orders").findAll({filter : ["customer_id", "order_id", "order_date"]})
.then(function (res) {
ordersCollectionData = res;
document.getElementById("orders").innerHTML = JSON.stringify(res, null, "\t");
})
.fail(function (err) {
console.log("There was a problem at " + JSON.stringify(err));
});
},0);
};
You may have noticed the async.queue method. This method derives from the async utility library. Queue will allow each task to run asynchronously and push will add the tasks to the queue. You will notice that I add both the data and the additional search field. Lastly, drain will run after the last task has been run. I added a setTimeout to keep the tasks from running too fast but it may be different for you.
The collection generated would look something like this.
| customer_id | order_id | order_date |
|---|---|---|
| 1 | 462 | 1-1-2000 |
| 2 | 608 | 2-2-2001 |
| 3 | 898 | 3-3-2002 |
This process goes the same for the next collection which would produce a collection like this.
| customer_id | customer_name | contact_name | country |
|---|---|---|---|
| 1 | Customer1 | Contact1 | USA |
| 2 | Customer2 | Contact2 | China |
| 3 | Customer3 | Contact3 | Spain |
I would like to add that the concurrency of the operation queue is set to one because the JavaScript-only implementation of the JSONStore API which is only meant for development expects synchronous operations. However, all the production ready implementation of the JSONStore API (i.e. Android, iOS, WP8 and W8) support the execution of various operations in parallel hence you may increase that number if desired without adverse consequences. Here's some code that should work everywhere:
1
2
3
4
5
6
7
8
9
10
11
12
var env = WL.Client.getEnvironment();
var CONCURRENCY = 1;
if (env === WL.Environment.IPHONE ||
env === WL.Environment.IPAD ||
env === WL.Environment.ANDROID ||
env === WL.Environment.WINDOWS8 ||
env === WL.Environment.WINDOWS_PHONE_8) {
CONCURRENCY = 2;
}
var q = async.queue(function (task, callback) {
//[...]
}, CONCURRENCY);
Grab documents for merging
Make sure to retrieve the documents from your collection filtering out the search fields and additional search fields you will need to merge. You can see how I do this above or you can look below.
1
2
3
4
WL.JSONStore.get('orders').findAll({filter : ['customer_id', 'order_id', 'order_date']})
.then(function (res) {
ordersCollectionData = res;
});
Merge the collections together
There are many ways in which you can merge these collections together. What you are merging is simply arrays of JSON objects. I used the merge method in lodash.
1
2
3
4
5
6
7
var shared_index = 'customer_id';
var mergedCollection = [];
mergedCollection =_.merge(customerCollectionData, ordersCollectionData, function(a, b){
if(_.isObject(a) && (a[shared_index] == b[shared_index])) {
return _.merge({}, a, b);
}
});
This would produce a collection similar to this.
| customer_id | customer_name | contact_name | country | order_id | order_date |
|---|---|---|---|---|---|
| 1 | Customer1 | Contact1 | USA | 462 | 1-1-2000 |
| 2 | Customer2 | Contact2 | China | 608 | 2-2-2001 |
| 3 | Customer3 | Contact3 | Spain | 898 | 3-3-2002 |
Hopefully this blog post will help you in joining collections. Of course this is just a full join.
Inclusive terminology note: The Mobile First Platform team is making changes to support the IBM® initiative to replace racially biased and other discriminatory language in our code and content with more inclusive language. While IBM values the use of inclusive language, terms that are outside of IBM's direct influence are sometimes required for the sake of maintaining user understanding. As other industry leaders join IBM in embracing the use of inclusive language, IBM will continue to update the documentation to reflect those changes.