Working with JSONStore Collections (Join)

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.

MobileFirst Platform Tutorial JSONStore Join App

Last modified on May 01, 2016
Share this post: