Equivalent of a SQL Full Join with Arrays of Javascript Objects

Question

I know that there are quite a lot of "Merge two arrays of objects in JS" questions, and I've read most of them. a few most similar to what I'm trying to do are:

How can I merge properties of two JavaScript objects dynamically?

Native javascript - merge two arrays of objects

How to merge two array of objects SQL style JOIN on JSON data

My issue is different because I'm trying to do a full SQL join, where the arrays will be different sizes, and will have new columns.

For example:

JSON1 = [{Color:"Blue", ID:"15", Size:"Large",Shape:"Square"},
         {Color:"Red", ID:"9", Size:"Medium",Shape:"Circle"},
         {Color:"Red", ID:"2", Size:"Large",Shape:"Triangle"},
         {Color:"Yellow", ID:"3", Size:"Small",Shape:"Square"}];

JSON2 = [{Color:"Blue", Name:"Henry", Inches:"51"},
         {Color:"Red", Name:"Jane", Inches:"7"},
         {Color:"Pink", Name:"Jack", Inches:"14"}];

Desired Output:

OUTPUT =[{Color:"Blue", ID:"15", Size:"Large",Shape:"Square",Name:"Henry", Inches:"51"},
         {Color:"Red", ID:"9", Size:"Medium",Shape:"Circle",Name:"Jane", Inches:"7"},
         {Color:"Red", ID:"2", Size:"Large",Shape:"Triangle",Name:"Jane", Inches:"7"},
         {Color:"Yellow", ID:"3", Size:"Small",Shape:"Square",Name:null, Inches:null},
         {Color:"Pink", ID:null, Size:null,Shape:null,Name:"Jack", Inches:"14"}];

So, similar to a full SQL join, I want the output JSON to contain all columns, matched when there is a match, but a new row if a key:value pair in the second JSON doesn't match any of the ones in any of the objects in the first.

What I have so far is below. It generally works but has a couple issues. I'm merging on a specific pre-defined value, and it would be nice for the function to figure out where the matching value was. Also, my function fails if I add more than one new property column to JSON2(i.e. it works if JSON2 has Color and Inches, but not Color, Inches, and Name.) Because I'm just hashing one property to one other.

var hash={};
for(var e in JSON2){
    hash[JSON2[e]["Color"]]= JSON2[e]["Inches"];
}
var trackHash = hash;
for(var k in JSON1){
    JSON1[k]["Inches"] = hash[JSON1[k]["Color"]];
    if(hash[JSON1[k]["Color"]]===undefined){
        delete trackHash[JSON1[k]["Color"]];
    }
}
for(var obj in JSON2){
    if(trackHash[JSON2[obj]["Color"]]!==undefined){
        JSON1.push(JSON2[obj]);
    }
}

Note that there is *no* JSON shown in your question. Your input and output are both just arrays of objects. — nnnnnn, Oct 18 '16 at 01:24
@Acoustic77 still waiting for your thoughts on the answer below :) — kukkuz, Oct 19 '16 at 14:38
Sorry, saw your answer but a friend was able to help me do it in linear time, so I will try to post that answer — singmotor, Oct 21 '16 at 03:47
@Acoustic77 I'm curious to see that answer you got in linear time - could you please post that? I believe I can learn something from it, thanks! — kukkuz, Oct 27 '16 at 02:14

score 3 · Answer 1 · answered Oct 18 '16 at 01:00

I think this might be what your after,..

It could be optimized etc, but hopefully this is a start.

Oh, for simplicity I'm also using Object.assign, so be aware for old browsers you might need a polyfill, or use something like lodash.

var JSON1 = [{Color:"Blue", ID:"15", Size:"Large",Shape:"Square"},
         {Color:"Red", ID:"9", Size:"Medium",Shape:"Circle"},
         {Color:"Red", ID:"2", Size:"Large",Shape:"Triangle"},
         {Color:"Yellow", ID:"3", Size:"Small",Shape:"Square"}];

var JSON2 = [{Color:"Blue", Name:"Henry", Inches:"51"},
         {Color:"Red", Name:"Jane", Inches:"7"},
         {Color:"Pink", Name:"Jack", Inches:"14"}];

function fullJoin(a, b) {
  var r = [];
  a.forEach(function (a) {
    var found = false;
    b.forEach(function (b) {
      if (a.Color === b.Color) {
        var j = Object.assign(a, b);
        r.push(j);
        found = true;
      }
    })
    if (!found) r.push(a);
  });
  b.forEach(function (b) {
    var found = false;
    a.forEach(function (a) {
       if (a.Color === b.Color) found = true;
    });
    if (!found) r.push(b);
  });
  return r;
}

var a = fullJoin(JSON1, JSON2);
a.forEach(function (a) { console.log(JSON.stringify(a)); });

kukkuz · Answer 2 · 2016-10-19T06:25:58.153

So here is what I did with the arrays:

Use Array.prototype.map to create a new array from JSON1 by copying in the key/value's for the corresponding similar keys in JSON2
Also tracked the keys in JSON2 that is not in JSON1 and added it to the result thereafter.

var JSON1 = [{Color:"Blue", ID:"15", Size:"Large",Shape:"Square"},
         {Color:"Red", ID:"9", Size:"Medium",Shape:"Circle"},
         {Color:"Red", ID:"2", Size:"Large",Shape:"Triangle"},
         {Color:"Yellow", ID:"3", Size:"Small",Shape:"Square"}];

var JSON2 = [{Color:"Blue", Name:"Henry", Inches:"51"},
         {Color:"Red", Name:"Jane", Inches:"7"},
         {Color:"Pink", Name:"Jack", Inches:"14"}];


var excluded = {};

// Join JSON2 to JSON1
// also track elements in JSON2 not in JSON1
var result = JSON1.map(function(a) {
      JSON2.forEach(function(element, index, array) {
        if (a.Color === element.Color) {
          Object.keys(element).forEach(function(key) {
            a[key] = element[key];
          });
        } else {
            this.visited = this.visited + 1 || 0; 
            if(this.visited == array.length)  {
              this.found = this.found || [];
              this.found.push(element);
            }
        }
      }, this);
      this.visited = 0;
        return a;
      }, excluded);

// add elements in JSON2 not in JSON1
if(excluded.found) {
   excluded.found.forEach(function(element) {
       result.push(element);
   });
}

console.log(result);

.as-console-wrapper{top:0;max-height:100%!important;}

Let me know your feedback if this worked for you. Thanks!

Equivalent of a SQL Full Join with Arrays of Javascript Objects

2 Answers2