16

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.

Crashthatch
  • 1,283
  • 2
  • 13
  • 20

9 Answers9

9

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
    })
);
Ashley Wilson
  • 484
  • 4
  • 13
  • 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. – John Pankowicz Sep 06 '22 at 00:06
  • @JohnPankowicz Not sure what you mean by "name of the card id"; could you please provide example data? – Ashley Wilson Sep 08 '22 at 10:59
  • 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. – John Pankowicz Sep 09 '22 at 14:15
  • @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: https://codepen.io/lifenstein/pen/VwxWjxL?editors=0012 (The other solution isn't generic though) – Ashley Wilson Sep 20 '22 at 04:29
  • You could add the object (table) names as prefixes for the keys to force them to be unique. – Ashley Wilson Sep 20 '22 at 04:39
6

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.

agershun
  • 4,077
  • 38
  • 41
5

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

Geoff
  • 9,340
  • 7
  • 38
  • 48
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");
zs2020
  • 53,766
  • 29
  • 154
  • 219
4

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),[]);
2

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;
        });
    });
actual_kangaroo
  • 5,971
  • 2
  • 31
  • 45
0

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"
 }
]
Will Lovett
  • 1,241
  • 3
  • 18
  • 35
0

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)))]
-1

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;
            }
        });
     });
MaxRocket
  • 920
  • 1
  • 12
  • 26