javascript - 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.

Answer 1

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