SQL style JOIN on JSON data

Question

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.

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, Aug 31 '11 at 00:25
Who says they're JS objects? The OP could be referring to two JSON strings. — Michael Mior, Aug 31 '11 at 02:56
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, Aug 31 '11 at 16:51

agershun · Answer 1 · 2014-12-15T21:18:56.473

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

Ray Toal · Answer 2 · 2011-08-31T01:25:55.353

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.

score 3 · Accepted Answer · edited Aug 18 '18 at 06:53

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);
    });
}

But the question says the arrays may not be in the same order, so you'd need to match on the provided numeric IDs, not based on array index. — nnnnnn, Aug 31 '11 at 00:32
I have mentioned in my answer to try it only if they match. Since they are going to be huge number of elements its better if we avoid the conditions as much as possible on the client side. — ShankarSangoli, Aug 31 '11 at 00:34
I edited the question to say that they are in the same order, since adding a sort tothe various SQL statements is trivial. — Luke The Obscure, Aug 31 '11 at 16:54
If they are in the same order then you can very well use my solution. — ShankarSangoli, Aug 31 '11 at 17:24

SQL style JOIN on JSON data

3 Answers3

Linked