Is there any way efficiently to join JSON data? Suppose we have two JSON datasets:

{"COLORS":[[1,red],[2,yellow],[3,orange]]}

{"FRUITS":[[1,apple],[2,banana],[3,orange]]}

And I want to turn this into the following client side:

{"NEW_FRUITS":[[1,apple,red],[2,banana,yellow],[3,orange,orange]]}

Keep in mind there will be thousands of records here with much more complex data structures. jQuery and vanilla javascript are both fine. Also keep in mind that there may be colors without fruits and fruits without colors.

NOTE: For the sake of simplicity, let's say that the two datasets are both in the same order, but the second dataset may have gaps.

Is there any way efficiently to join JSON data? Suppose we have two JSON datasets:

{"COLORS":[[1,red],[2,yellow],[3,orange]]}

{"FRUITS":[[1,apple],[2,banana],[3,orange]]}

And I want to turn this into the following client side:

{"NEW_FRUITS":[[1,apple,red],[2,banana,yellow],[3,orange,orange]]}

Keep in mind there will be thousands of records here with much more complex data structures. jQuery and vanilla javascript are both fine. Also keep in mind that there may be colors without fruits and fruits without colors.

NOTE: For the sake of simplicity, let's say that the two datasets are both in the same order, but the second dataset may have gaps.

• javascript
• jquery
• json
• join

• Good question, but you don't have two JSON datasets, you have two JavaScript objects; you don't want to join JSON data, you want to join objects. – nnnnnn Commented Aug 31, 2011 at 0:25
• Who says they're JS objects? The OP could be referring to two JSON strings. – Michael Mior Commented Aug 31, 2011 at 2:56
• 1 The fine details about the differences are lost on me, but I can tell you that I am presenting a heavily truncated version of what I'm working with. – Luke The Obscure Commented Aug 31, 2011 at 16:51

3 Answers 3

Alasql JavaScript SQL library does exactly what you need in one line:

 <script src="alasql.min.js"></script>
 <script>
    var data = { COLORS: [[1,"red"],[2,"yellow"],[3,"orange"]],            
                 FRUITS: [[1,"apple"],[2,"banana"],[3,"orange"]]};

    data.NEW_FRUITS = alasql('SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] \
         FROM ? AS COLORS JOIN ? AS FRUITS ON COLORS.[0] = FRUITS.[0]',
         [data.COLORS, data.FRUITS]);
 </script>

You can play with this example in jsFiddle.

This is a SQL expression, where:

• SELECT - select operator
• MATRIX - modifier, whci converts resultset from array of objects to array of arrays
• COLORS.[0] - first column of COLORS array, etc.
• FRUITS.1 AS 2 - the second column of array FRUITS will be stored as third column in resulting recordset
• FROM ? AS COLORS - data array from parameters named COLORS in SQL statement
• JOIN ? ON ... - join
• [data.COLORS, data.FRUITS] - parameters with data arrays

The fact that there will be thousands of inputs and the keys are not necessarily ordered means your best bet (at least for large objects) is to sort by key first. For objects of size less than about 5 or so, a brute-force n^2 approach should suffice.

Then you can write out the result by walking through the two arrays in parallel, appending new "records" to your output as you go. This sort-then-merge idea is a relatively powerful one and is used frequently. If you do not want to sort first, you can add elements to a priority queue, merging as you go. The sort-then-merge approach is conceptually simpler to code perhaps; if performance matters you should do some profiling.

For colors-without-fruits and fruits-without-colors, I assume writing null for the missing value is sufficient. If the same key appears more than once in either color or fruit, you can either choose one arbitrarily, or throw an exception.

ADDENDUM I did a fiddle as well: http://jsfiddle.net/LuLMz/. It makes no assumptions on the order of the keys nor any assumptions on the relative lengths of the arrays. The only assumptions are the names of the fields and the fact that each subarray has two elements.

There is not a direct way, but you can write logic to get a combined object like this. Since "apple, red, banana...." are all strings, they should be wrapped in a single or double quote.

If you can match the COLORS and FRUITS config array by adding null values for missing items then you can use this approach.

Working demo

var colors = {"COLORS":[[1,'red'],[2,'yellow'],[3,'orange']]}

var fruits = {"FRUITS":[[1,'apple'],[2,'banana'],[3,'orange']]}

var newFruits = {"NEW_FRUITS": [] }

//Just to make sure both arrays are the same size, otherwise the logic will break
if(colors.COLORS.length == fruits.FRUITS.length){
    var temp;
    $.each(fruits.FRUITS, function(i){
        temp = this;
        temp.push(colors.COLORS[i][2]);
        newFruits.NEW_FRUITS.push(temp);
    });
}

Alternatively, if you can create colors and fruits configs as an array of objects, instead of an array of arrays, you can try this solution. The sequence of the elements is irrelevant here, but the array size should still match.

Working demo

var colors = {"COLORS":[ {"1": 'red'}, { "2": 'yellow'}, {"3":'orange'}]}

var fruits = {"FRUITS":[ {"1":'apple'}, { "2": 'banana'}, {"3":'orange'}]}

var newFruits = {"NEW_FRUITS": [] }

if(colors.COLORS.length == fruits.FRUITS.length){
    var temp, first;
    $.each(fruits.FRUITS, function(i){
        for(first in this)break;
        temp = {};
        temp[first] = [];
        temp[first].push(this[first]);
        temp[first].push(colors.COLORS[i][first]);
        newFruits.NEW_FRUITS.push(temp);
    });
}