Basic Join in MongoDB

Question

I'm trying to do a join/merge of two collections, as per the following post on Stack Overflow: How do I perform the SQL Join equivalent in MongoDB?

By user: Orlando Becerra

I understand that MongoDB is non-relational, and since V3.2 has some, but limited abilities to do this.

Can you let me know if it is possible to do what I'm trying below (eg, I'm on the right path), or if it's not possible .. or another approach is best?

Here is my collection: allocations

{
"_id" : ObjectId("58a65c082c5fc49016c6a3cd"),
"data" : [ 
    {
        "version" : 0,
        "jobTaskId" : {
            "id" : 16089453
        },
        "endTime" : "2017-02-17T01:14:00.000+0000",
        "minutes" : 210,
        "trafficEmployeeId" : {
            "id" : 3422
        }
    },
        {
        "version" : 1,
        "jobTaskId" : {
            "id" : 16089453
        },
        "endTime" : "2017-02-16T01:14:00.000+0000",
        "minutes" : 400,
        "trafficEmployeeId" : {
            "id" : 3422
    }
  }
]
}

Here is my collection: employees

{
"_id" : ObjectId("58a66cc0c76ed0f7e9f52d0e"),
"data" : [
    {
        "version" : 67,
        "userName" : "Jimjeff2",
        "employeeDetails" : {
            "id" : 3422,
            "version" : 135
            },
            "personalDetails" : {
                "id" : 24487,
                "version" : 32,
                "firstName" : "Jim",
                "lastName" : "Jeffrey"
            }
    },
    {
        "version" : 37,
        "userName" : "sandyub2",
        "employeeDetails" : {
            "id" : 3562,
            "version" : 15
            },
            "personalDetails" : {
                "id" : 24487,
                "version" : 32,
                "firstName" : "Sandy",
                "lastName" : "Mason"
            }
    }
]
}

So, I'm trying to match the array of many allocations in a collection using trafficEmployeeId.id, to the corresponding employee collection using employeeDetails.id

I am using this function:

db.allocations.find().forEach(
function (findAllocations) {
    findAllocations.employees = db.employees.findOne( { "trafficEmployeeId.id": findAllocations.trafficEmployeeId.id } );
    findAllocations.allocations = db.allocations.find( { "employeeDetails.id": findAllocations.employeeDetails.id  } ).toArray();
    db.allocationsReloaded.insert(findAllocations);
}
);
db.allocationsReloaded.find().pretty()

I am getting back results:

TypeError: findAllocations.trafficEmployeeId is undefined :
@(shell):3:61
DBQuery.prototype.forEach@src/mongo/shell/query.js:477:1
@(shell):1:1

score 0 · Answer 1 · answered Feb 17 '17 at 16:42

Hello I see you're having problem using promises. Every db call is asynchronous so you need to ensure you have a result before you use the result. One way is to use the functions you need inside a callback. For example:

db.find({_id:"id"},function(data, error) { //your code that depends the variables here})

But, for your case, what I would suggest is to use the $lookup function:

Here is a code example:

db.allocations.aggregate([ 
{$match: {"yourSearchParameters"}}, 
{$lookup : {from:"employees",localField:"trafficEmployeeId",foreignField:"employeeDetails.id",as:"employees"}}])

Hope my answer was helpful

Basic Join in MongoDB

1 Answers1