Joins in Javascript

Question

I have 2 lists of objects:

people = 
[{id: 1, name: "Tom", carid: 1},
 {id: 2, name: "Bob", carid: 1},
 {id: 3, name: "Sir Benjamin Rogan-Josh IV", carid: 2}];

cars=
[{id: 1, name: "Ford Fiesta", color: "blue"},
 {id: 2, name: "Ferrari", color: "red"},
 {id: 3, name: "Rover 25", color: "Sunset Melting Yellow with hints of yellow"}];

Is there a function (possibly in Angular, JQuery, Underscore, LoDash, or other external library) to do a left join in one line on these? Something like:

peoplewithcars = leftjoin( people, cars, "carid", "id");

I can write my own, but if LoDash has an optimised version I'd like to use that.

Tolidine

Answer 1

This implementation uses the ES6 spread operator. Again, not a library function as asked for.

const leftJoin = (objArr1, objArr2, key1, key2) => objArr1.map(
    anObj1 => ({
        ...objArr2.find(
            anObj2 => anObj1[key1] === anObj2[key2]
        ),
        ...anObj1
    })
);

Spirituel

Clever solution. But it fails if we try to use "carid" as the name of the card id in both tables. And I don't see an easy way to fix this in the code. The solution by @ЕрланЯр-Мухамедов solves this. – Milliliter

@JohnPankowicz Not sure what you mean by "name of the card id"; could you please provide example data? – Spirituel

It's much clearer when designing data structures to use the same property name in each place for the same property. Therefore, the 1st member of the "cars" array would be: [{carid: 1, name: "Ford Fiesta" ... But when I change "id" to "carid" in cars, your leftjoin fails. I'm sure there's a way to fix this, but I couldn't see how. I ended up using ЕрланЯр's solution in my code. – Milliliter

@JohnPankowicz As with any left join, the first table takes precedence, so if the same key appears in both objects, the keys on the right object get overwritten. If you have matching sets, you can reverse the order of the tables to get the combined set. See codepen: codepen.io/lifenstein/pen/VwxWjxL?editors=0012 (The other solution isn't generic though) – Spirituel

You could add the object (table) names as prefixes for the keys to force them to be unique. – Spirituel

Answer 2

You can use Alasql JavaScript SQL library to join two or more arrays of objects:

var res = alasql('SELECT people.name AS person_name, cars.name, cars.color \
    FROM ? people LEFT JOIN ? cars ON people.carid = cars.id',[people, cars]);

Try this example at jsFiddle.

Iggy

Answer 3

Linq.js http://linqjs.codeplex.com/ will do joins along with many other things

Unship

Answer 4

It is not hard to implement using underscore.js

function leftJoin(left, right, left_id, right_id) {
    var result = [];
    _.each(left, function (litem) {
        var f = _.filter(right, function (ritem) {
            return ritem[right_id] == litem[left_id];
        });
        if (f.length == 0) {
            f = [{}];
        }
        _.each(f, function (i) {
            var newObj = {};
            _.each(litem, function (v, k) {
                newObj[k + "1"] = v;
            });
            _.each(i, function (v, k) {
                newObj[k + "2"] = v;
            });
            result.push(newObj);
        });
    });
    return result;
}

leftJoin(people, cars, "carid", "id");

Indoaryan

Answer 5

You can do such stuff in plain javascript.

people.map(man => 
        cars.some(car => car.id === man.carid) ? 
            cars.filter(car => car.id === man.carid).map(car => ({car, man})) : 
            {man}
        ).reduce((a,b)=> a.concat(b),[]);

Matthewmatthews

Also a very clever solution. And it solves the problem I had with the one from @AshleyWilson – Milliliter

Answer 6

No, LoDash does not have join it's prety easy to implement your own though, this isn't quite a join but selects all people with a matching car:

    var peopleWithCars = _.filter(people, function (person) {
        return _.exists(cars, function(car) {
            return car.id === person.id;
        });
    });

Pinnule

Answer 7

This example uses Lodash to left join the first matched object. Not quite what the question asks, but I found a similar answer helpful.

var leftTable = [{
  leftId: 4,
  name: 'Will'
}, {
  leftId: 3,
  name: 'Michael'
}, {
  leftId: 8,
  name: 'Susan'
}, {
  leftId: 2,
  name: 'Bob'
}];

var rightTable = [{
  rightId: 1,
  color: 'Blue'
}, {
  rightId: 8,
  color: 'Red'
}, {
  rightId: 2,
  color: 'Orange'
}, {
  rightId: 7,
  color: 'Red'
}];

console.clear();

function leftJoinSingle(leftTable, rightTable, leftId, rightId) {
  var joinResults = [];

  _.forEach(leftTable, function(left) {
  	      var findBy = {};
      findBy[rightId] = left[leftId];

      var right = _.find(rightTable, findBy),
          result = _.merge(left, right);

      joinResults.push(result);
  })

  return joinResults;
}


var joinedArray = leftJoinSingle(leftTable, rightTable, 'leftId', 'rightId');
console.log(JSON.stringify(joinedArray, null, '\t'));

Results

[
	{
		"leftId": 4,
		"name": "Will"
	},
	{
		"leftId": 3,
		"name": "Michael"
	},
	{
		"leftId": 8,
		"name": "Susan",
		"rightId": 8,
		"color": "Red"
	},
	{
		"leftId": 2,
		"name": "Bob",
		"rightId": 2,
		"color": "Orange"
	}
]

Mark

Answer 8

Array

conap = ['1', '2', '7'];
avenida = ['1', '3', '5', '9'];

Union

union = [...conap, ...avenida]

Join

left_join = conap.filter((x) => !avenida.includes(x))

right_join = avenida.filter((x) => !conap.includes(x))
    
full_outer_join = [...(conap.filter((x) => !avenida.includes(x))), ...(avenida.filter((x) => !conap.includes(x)))]

Displayed

Answer 9

Here's a simple loop I did for a Javascript (JQuery in this case) to "join" obj1 and obj2 on someID and add one property from obj2 to obj1.

If you want to do a more complete join, you can go through and expand it to loop on obj2.hasOwnProperty() and copy that over as well.

    $.each(obj1,function(i){
        $.each(obj2, function(k){
            if (obj2[k].someID == obj1[i].someID ){
                obj1[i].someValue = obj2[k].someValue;
            }
        });
     });

Ocana