Is there some way I can "join" the contents of two javascript arrays much like I would do a join in SQL

Question

I have two arrays: Question and UserProfile

• The userProfiles: [] array contain { id, name } objects
• The questions: [] array contains { id, text, createdBy } objects

The createdBy integer in questions is always one of the id values in userProfiles.

Is there a way I could "join" the arrays in much the same way as I would join up two SQL tables if I was using a database.

What I need as an end result is an array that contains

{ id, text, name }

Corresponding SQL would be:

SELECT u.id, q.text, u.name 
FROM userProfiles u 
JOIN questions q ON q.createdBy=u.id

Answer 1

I think what you want is an inner join, which is simple enough to implement in JavaScript:

const innerJoin = (xs, ys, sel) =>
    xs.reduce((zs, x) =>
    ys.reduce((zs, y) =>        // cartesian product - all combinations
    zs.concat(sel(x, y) || []), // filter out the rows and columns you want
    zs), []);

For the purpose of demonstration we'll use the following data set (thank you @AshokDamani):

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

This is how you would use it:

const result = innerJoin(userProfiles, questions,
    ({id: uid, name}, {id, text, createdBy}) =>
        createdBy === uid && {id, text, name});

In SQL terms this would be similar to:

SELECT questions.id, questions.text, userProfiles.name
FROM userProfiles INNER JOIN questions
ON questions.createdBy = userProfiles.id;

Putting it all together:

const innerJoin = (xs, ys, sel) =>
    xs.reduce((zs, x) =>
    ys.reduce((zs, y) =>        // cartesian product - all combinations
    zs.concat(sel(x, y) || []), // filter out the rows and columns you want
    zs), []);

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

const result = innerJoin(userProfiles, questions,
    ({id: uid, name}, {id, text, createdBy}) =>
        createdBy === uid && {id, text, name});

console.log("Open your browser console to see the output.");

console.table(result);

---

Edit: However this is not the best solution. Since the above solution loops through the Cartesian product it takes O(m × n) time to run. With a little bit of modification we can make it run in O(m + n) time - @pebbl found it first:

const equijoin = (xs, ys, primary, foreign, sel) => {
    const ix = xs.reduce((ix, row) => // loop through m items
        ix.set(row[primary], row),    // populate index for primary table
    new Map);                         // create an index for primary table

    return ys.map(row =>              // loop through n items
        sel(ix.get(row[foreign]),     // get corresponding row from primary
        row));                        // select only the columns you need
};

Now you could use it as follows:

const result = equijoin(userProfiles, questions, "id", "createdBy",
    ({name}, {id, text}) => ({id, text, name}));

Putting it all together:

const equijoin = (xs, ys, primary, foreign, sel) => {
    const ix = xs.reduce((ix, row) => ix.set(row[primary], row), new Map);
    return ys.map(row => sel(ix.get(row[foreign]), row));
};

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

const result = equijoin(userProfiles, questions, "id", "createdBy",
    ({name}, {id, text}) => ({id, text, name}));

console.log("Open your browser console to see the output.");

console.table(result);

Answer 2

This seems to be an important general-purpose question, and although there are many answers, some have borderline behavior like modifying the existing data, solving a completely different problem than the issue at hand, using up to 93,057 bytes of JavaScript (not to mention producing the wrong result), producing overly complex additional nesting of data structures, requiring a lot of code on each invocation, and most seriously, not being a self-contained solution to the important more general-purpose problem at the heart of this question.

So for better or for worse, I wrote a shim that extends the JavaScript Array object with a method .joinWith intended to be used in order to join this array of objects with that array of objects, by a common indexing field. It is possible to select a list of fields desired in output (good for merging arrays of objects with many fields when only a few are wanted) or to omit a list of fields in output (good for merging arrays of objects when most fields are desired but a few are not).

The shim code isn't made to look pretty, so it will be at the end, with the example of how to use it for the OP's particular kind of data coming first:

/* this line will produce the array of objects as desired by the OP */
joined_objects_array = userProfiles.joinWith(questions, 'id', ['createdBy'], 'omit');

/* edit: I just want to make 100% sure that this solution works for you, i.e.,
 *       does exactly what you need. I haven't seen your actual data, so it's
 *       possible that your IDs are are not in common, (i.e., your createdBy
 *       is in common like you said, but not the IDs, and if so you could
 *       morph your data first like this:
 * questions.map(function(x) { x.id = x.createdBy; });
 *       before joining the arrays of objects together.
 *
 */

The following code is for demonstration:

var array1 = [{ id: 3124, name: 'Mr. Smith' },
              { id: 710, name: 'Mrs. Jones' }];
var array2 = [{ id: 3124, text: 'wow', createdBy: 'Mr. Jones' },
              { id: 710, text: 'amazing' }];

var results_all = array1.joinWith(array2, 'id');

// [{id:3124, name:"Mr. Smith", text:"wow", createdBy:"Mr. Jones"},
// {id:710, name:"Mrs. Jones", text:"amazing"}]*

var results_selected = array1.joinWith(array2, 'id', ['id', 'text', 'name']);

// [{id:3124, name:"Mr. Smith", text:"wow"},
// {id:710, name:"Mrs. Jones", text:"amazing"}]*

/* or equivalently, */
var results_omitted = array1.joinWith(array2, 'id', ['createdBy'], 1);

// [{id:3124, name:"Mr. Smith", text:"wow"},
// {id:710, name:"Mrs. Jones", text:"amazing"}]*

There are some other nice things this solution does (one of them is preserving the ability to access the resulting data by its indexing key, despite returning an array).

Enjoy!

/* Array.joinWith - shim by Joseph Myers 7/6/2013 */


if (!Array.prototype.joinWith) {
    +function () {
        Array.prototype.joinWith = function(that, by, select, omit) {
            var together = [], length = 0;
            if (select) select.map(function(x){select[x] = 1;});
            function fields(it) {
                var f = {}, k;
                for (k in it) {
                    if (!select) { f[k] = 1; continue; }
                    if (omit ? !select[k] : select[k]) f[k] = 1;
                }
                return f;
            }
            function add(it) {
                var pkey = '.'+it[by], pobj = {};
                if (!together[pkey]) together[pkey] = pobj,
                    together[length++] = pobj;
                pobj = together[pkey];
                for (var k in fields(it))
                    pobj[k] = it[k];
            }
            this.map(add);
            that.map(add);
            return together;
        }
    }();
}

Documentation:

        /* this and that both refer to an array of objects, each containing
           object[by] as one of their fields */
        /*
         N.B. It is the responsibility of the user of this method
         to ensure that the contents of the [by] fields are
         consistent with each other between the two arrays!
        */
        /* select is an array of field names to be included in the resulting
           objects--all other fields will be excluded, or, if the Boolean value
           of omit evaluates to true, then select is an array of field names to
           be excluded from the resulting objects--all others will be included.
        */

Answer 3

i just about always use underscore.js because it has such good support for arrays and "map reduce" which this problem can be solved with.

here is a fiddle with a solution for your question ( it assumes there is only one question per user as your original post suggests)

http://jsfiddle.net/x5Z7f/

(open the browser console to see the output)

    var userProfiles = [{ id:'1', name:'john' }, { id:'2', name:'mary' }];

var questions =[ { id:'1', text:'question john', createdBy:'1' }, { id:'2', text:'question mary', createdBy:'2' }];

var rows = _.map(userProfiles, function(user){ 
    var question = _.find(questions, function(q){ return q.createdBy == user.id });
    user.text = question? question.text:'';
    return user; 
})

_.each(rows, function(row){ console.log(row) });

the above answer assumes you are using id == createdBy as the joining column.

Answer 4

If it were me, I'd approach this in the following manner:

The set-up:

var userProfiles = [], questions = [];

userProfiles.push( {id:1, name:'test'} );
userProfiles.push( {id:2, name:'abc'} );
userProfiles.push( {id:3, name:'def'} );
userProfiles.push( {id:4, name:'ghi'} );

questions.push( {id:1, text:'monkey', createdBy:1} );
questions.push( {id:2, text:'Monkey', createdBy:1} );
questions.push( {id:3, text:'big',    createdBy:2} );
questions.push( {id:4, text:'string', createdBy:2} );
questions.push( {id:5, text:'monKey', createdBy:3} );

First, would be to create a look-up object, where the linking id is used as a key

var createObjectLookup = function( arr, key ){
  var i, l, obj, ret = {};
  for ( i=0, l=arr.length; i<l; i++ ) {
    obj = arr[i];
    ret[obj[key]] = obj;
  }
  return ret;
};

var up = createObjectLookup(userProfiles, 'id');

Now that you have this, it should be easy to step through the questions, and find your user object to merge:

var i, l, question, user, result = [];
for ( i=0, l=questions.length; i<l; i++ ) {
  if ( (question = questions[i]) && (user = up[question.createdBy]) ) {
    result.push({
      id: question.id,
      text: question.text,
      name: user.name
    });
  }
}

You should now have everything you need in result

console.log(result);

Answer 5

A simple way to do the SQL joins in JavaScript:

let userProfiles = [ { id: 3, name: "Paquito"}, { id: 2, name: "Jaime" } ];
let questions = [ { id: 22, text: "My question", createdBy: 3 }, { id: 44, text: "Other question", createdBy: 5 } ];

let left_join = questions
.map ( q => ({ ...userProfiles.find( u => q.createdBy === u.id ), ...q }) );

document.write("<p>Left join: <br>", JSON.stringify(left_join));

let right_join = userProfiles
.map ( u => ({ ...questions.find( q => q.createdBy === u.id ), ...u }) );;

document.write("</p><p>Right join: <br>", JSON.stringify(right_join));

let inner_join = questions
.filter( q => userProfiles.find( u => q.createdBy === u.id ) )
.map ( q => ({ ...userProfiles.find( u => q.createdBy === u.id ), ...q }) );

document.write("</p><p>Inner join: <br>", JSON.stringify(inner_join));

Answer 6

This is my attempt to make a somehow generic solution. I'm using Array.map and the Array.index methods here:

var arr1 = [
    {id: 1, text:"hello", oid:2},
    {id: 2, text:"juhu", oid:3},
    {id: 3, text:"wohoo", oid:4},
    {id: 4, text:"yeehaw", oid:1}
];
var arr2 = [
    {id: 1, name:"yoda"},
    {id: 2, name:"herbert"},
    {id: 3, name:"john"},
    {id: 4, name:"walter"},
    {id: 5, name:"clint"}
];

function merge(arr1, arr2, prop1, prop2) {
    return arr1.map(function(item){
        var p = item[prop1];
        el = arr2.filter(function(item) {
            return item[prop2] === p;
        });
        if (el.length === 0) {
            return null;
        }
        var res = {};
        for (var i in item) {
            if (i !== prop1) {
                res[i] = item[i];
            }
        }
        for (var i in el[0]) {
            if (i !== prop2) {
                res[i] = el[0][i];
            }
        }
        return res;
    }).filter(function(el){
        return el !== null;
    });
}

var res = merge(arr1, arr2, "oid", "id");
console.log(res);

So basically you can define two arrays and one property for each array, so that prop1 will be replaced with all the properties of an item in array2 whose prop2 is equal to prop1.

The result in this case would be:

var res = [
    {id: 1, text:"hello", name:"herbert"},
    {id: 2, text:"juhu", name:"john"},
    {id: 3, text:"wohoo", name:"walter"},
    {id: 4, text:"yeehaw", name:"yoda"}
];

Note that if there is more then one match, the first item will be used and if there is no match, the object will be removed from the resulting array.

FIDDLE

Answer 7

all u want is the ResultArray calculated below:

    var userProfiles1= new Array(1, "ashok");
    var userProfiles2= new Array(2, "amit");
    var userProfiles3= new Array(3, "rajeev");

    var UArray = new Array(userProfiles1, userProfiles2, userProfiles3);

    var questions1= new Array(1, "text1", 2);
    var questions2= new Array(2, "text2", 2);
    var questions3= new Array(3, "text3", 1);
    var questions4= new Array(4, "text4", 2);
    var questions5= new Array(5, "text5", 3);
    var questions6= new Array(6, "text6", 3);

    var QArray = new Array(questions1, questions2, questions3, questions4, questions5, questions6);

    var ResultArray = new Array();

    for (var i=0; i<UArray.length; i++)
    {
        var uid = UArray[i][0];
        var name = UArray[i][1];

        for(var j=0; j<QArray.length; j++)
        {
            if(uid == QArray[j][2])
            {
                 var qid = QArray[j][0]
                 var text = QArray[j][1];

                 ResultArray.push(qid +"," + text +","+ name)
            }
        }    
    }

for(var i=0; i<ResultArray.length; i++)
    {
        document.write(ResultArray[i] + "<br>")
    }

demo : http://jsfiddle.net/VqmVv/

Answer 8

Just wanted to share some generic code:

// Create a cartesian product of the arguments.
// product([1,2],['a','b'],['X']) => [[1,"a","X"],[1,"b","X"],[2,"a","X"],[2,"b","X"]]
// Accepts any number of arguments.
product = function() {
    if(!arguments.length)
        return [[]];
    var p = product.apply(null, [].slice.call(arguments, 1));
    return arguments[0].reduce(function(r, x) {
        return p.reduce(function(r, y) {
            return r.concat([[x].concat(y)]);
        }, r);
    }, []);
}

Your problem:

result = product(userProfiles, questions).filter(function(row) {
    return row[0].id == row[1].createdBy;
}).map(function(row) {
    return {
        userName: row[0].name,
        question: row[1].text
    }
})

Answer 9

You can do this using reduce and map.

First, create a mapping from IDs to names:

var id2name = userProfiles.reduce(function(id2name, profile){
    id2name[profile.id] = profile.name;
    return id2name;
}, {});

Second, create a new array of questions but with the name of the user who created the question in place of their ID:

var qs = questions.map(function(q){
    q.createdByName = id2name[q.createdBy];
    delete q.createdBy;
    return q;
});

Answer 10

I don't know any built-in function allowing to do so.

You can program your own function, something similar to this jsFiddle :

var userProfiles = [{id:1, name:'name1'},{id:2,name:'name2'}];
    var questions = [
        {id:1, text:'text1', createdBy:'foo'},
        {id:1, text:'text2', createdBy:'bar'},
        {id:2, text:'text3', createdBy:'foo'}];

    merged = mergeMyArrays(userProfiles,questions);

    console.log(merged);
    /**
     * This will give you an array like this:
     * [{id:1, name:name1, text:text1}, {...]
     * params : 2 arrays to merge by id
     */
    function mergeMyArrays(u,q){
        var ret = [];
        for(var i = 0, l = u.length; i < l; i++){
            var curU = u[i];
            for(var j = 0, m = q.length; j<m; j++){
                if(q[j].id == curU.id){
                    ret.push({
                        id: curU.id,
                        name: curU.name,
                        text: q[j].text
                    });
                }
            }
        }
        return ret;
    }

Or if you want a better "join" (SQL-y) :

var userProfiles = [{id:1, name:'name1'},{id:2,name:'name2'}];
var questions = [
    {id:1, text:'text1', createdBy:'foo'},
    {id:1, text:'text2', createdBy:'bar'},
    {id:2, text:'text3', createdBy:'foo'}];

merged = mergeMyArrays(userProfiles,questions);

console.log(merged);
/**
 * This will give you an array like this:
 * [{id:1, name:name1, questions:[{...}]]
 * params : 2 arrays to merge by id
 */
function mergeMyArrays(u,q){
    var ret = [];
    for(var i = 0, l = u.length; i < l; i++){
        var curU = u[i],
            curId = curU.id,
            tmpObj = {id:curId, name:curU.name, questions:[]};
        for(var j = 0, m = q.length; j<m; j++){
            if(q[j].id == curId){
                tmpObj.questions.push({
                    text: q[j].text,
                    createdBy: q[j].createdBy
                });
            }
        }
        ret.push(tmpObj);
    }
    return ret;
}

Like in this jsFiddle

Answer 11

This is easily done with StrelkiJS

var userProfiles = new StrelkiJS.IndexedArray();
userProfiles.loadArray([
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"}
]);

var questions = new StrelkiJS.IndexedArray();
questions.loadArray([
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3}
]);

var res=questions.query([{
    from_col:  "createdBy", 
    to_table:  userProfiles, 
    to_col:    "id", 
    type:      "outer"
}]);

The result will be:

[
 [
  {"id":1,"text":"text1","createdBy":2},
  {"id":2,"name":"Amit"}
 ],
 [
  {"id":2,"text":"text2","createdBy":2},
  {"id":2,"name":"Amit"}
 ],
 [
  {"id":3,"text":"text3","createdBy":1},
  {"id":1,"name":"Ashok"}
 ],
 [
  {"id":4,"text":"text4","createdBy":2},
  {"id":2,"name":"Amit"}
 ],
 [
  {"id":5,"text":"text5","createdBy":3},
  {"id":3,"name":"Rajeev"}
 ],
 [
  {"id":6,"text":"text6","createdBy":3},
  {"id":3,"name":"Rajeev"}
 ]
]

Answer 12

Aadit's second O(m+n) solution looked good to me as concise, functional, and efficient. However, it didn't implement the duplication of records when multiple keys match that I needed for my use case, so I wrote this slight variation:

function equijoin(arrL,arrR,keyL,keyR=keyL){
        const idx = arrL.reduce(
                (idx, objL) => 
                        objL[keyL] === undefined
                        ?idx
                        :idx.set(objL[keyL], [...(idx.get(objL[keyL])||[]), ...[objL]]) 
                ,new Map
            )
        const matches = 
                arrR
                .map(objR =>
                        objR[keyR] === undefined
                        ? []
                        : idx.get(objR[keyR])
                                .map(objL => ({l:objL, r:objR}))
                                .reduce((a,b)=>a.concat(b),[])
                    )
                .reduce((a,b)=>a.concat(b),[])
        return matches
}

(Contrived) Example:

var players =[
    {id:"Alice", team:"Red"},
    {id:"Bob", team:"Red"},
    {id:"Clair", team:"Blue"},
    {id:"Dave"},
    {id:"Elliot"}
];

equijoin(players,players,"team")

[
{l:{id:"Alice",team:"Red"},r:{id:"Alice",team:"Red"}},
{l:{id:"Bob",team:"Red"},r:{id:"Alice",team:"Red"}},
{l:{id:"Alice",team:"Red"},r:{id:"Bob",team:"Red"}},
{l:{id:"Bob",team:"Red"},r:{id:"Bob",team:"Red"}},
{l:{id:"Clair",team:"Blue"},r:{id:"Clair",team:"Blue"}}
]

Answer 13

adding to Aadit M Shah's equijoin, there was a request for this to be similar to a left join. But as the method isn't a join, but a direct 1 to 1 equals, the method isn't exactly a left join, more a equijoin with a default if no match. To achieve this however and it make sense reading it, I reversed the method, as it was effectively joining in reverse.

const equijoinWithDefault = (xs, ys, primary, foreign, sel, def) => {
  const iy = ys.reduce((iy, row) => iy.set(row[foreign], row), new Map);
  return xs.map(row => typeof iy.get(row[primary]) !== 'undefined' ? sel(row, iy.get(row[primary])): sel(row, def));
};

example call:

const userProfiles = [
  {id: 1, name: "Ashok"},
  {id: 2, name: "Amit"},
  {id: 3, name: "Rajeev"},
];

const questions2 = [
  {id: 1, text: "text1", createdBy: 2},
  {id: 2, text: "text2", createdBy: 2},
  {id: 3, text: "text3", createdBy: 1},
  {id: 4, text: "text4", createdBy: 2},
  {id: 5, text: "text5", createdBy: 3},
  {id: 6, text: "text6", createdBy: 3},
  {id: 7, text: "text7", createdBy: 4},
];

let result2 = equijoinWithDefault(questions2, userProfiles, "createdBy", "id", ({id, text}, {name}) => ({id, text, name}), {name:null});

Answer 14

Thats another way

var array1 = [{ id: 1, name: 'Khikmat'},
              { id: 2, name: 'User'}];
var array2 = [{ id: 1, text: 'hi test' },
              { id: 2, text: 'amazing test' }, { id: 2, text: 'test'}];
 
 var result = array1.map((elem) => {
   elem["complaints"] = array2.filter((val) => {
       return val.id === elem.id; 
   });
   return elem;
 });

 
console.log(JSON.stringify(result))

Answer 15

A TypeScript version of @aadit-m-shah's answer:

export const equiJoin = <T, U, K>(
    xs: T[],
    ys: U[],
    primaryPredicate: (v: T) => unknown,
    foreignPredicate: (v: U) => unknown,
    selectorFn: (value: T|undefined, row: U) => K) =>
{
    const ix = xs.reduce<Map<unknown, T>>(
        (acc, row) => acc.set(primaryPredicate(row), row),
        new Map
    );
    
    return ys.map((row) => selectorFn(ix.get(foreignPredicate(row)), row));
};

How to use:

const userProfiles = [
    {id: 1, name: "Ashok"},
    {id: 2, name: "Amit"},
    {id: 3, name: "Rajeev"},
];

const questions = [
    {id: 1, text: "text1", createdBy: 2},
    {id: 2, text: "text2", createdBy: 2},
    {id: 3, text: "text3", createdBy: 1},
    {id: 4, text: "text4", createdBy: 2},
    {id: 5, text: "text5", createdBy: 3},
    {id: 6, text: "text6", createdBy: 3},
];

const joinedArrays = equiJoin(
    userProfiles,
    questions,
    (profile) => profile.id,
    (q) => q.createdBy,
    (p, {id, text}) => ({ id, text, name: p?.name || 'Unknown' })
);

Answer 16

User innerJoin from Ramda https://ramdajs.com/docs/#innerJoin which returns an array of common elements in two array args, based on a test function you provide.

Answer 17

You can use first jQuery.merge() and then jQuery.unique() to achieve this. merge() will add all the items in one array and unique() will remove the duplicates from that array.

http://api.jquery.com/jQuery.merge/

http://api.jquery.com/jQuery.unique/