How to perform $lookup between Stringified id and ObjectId in mongo?

2
votes

Scenario

I have to perform join on 2 collections named college and student.I am using MongoDB 3.4. Here, college has a field named studentId which references to _id of student collection. The problem is that,studentId is already stored in stringified form whereas _id is the ObjectId.

example of studentId: "5910193d4c00000a01c2c615"

example of _id: ObjectId("59a931696d00007c0962e24a")

So, when I tried following join query:

db.college.aggregate(
    {
        $lookup: {
           from: "student",
           localField: "studentId",
           foreignField: "_id",
           as: "Related"
        }
    })

It doesn't work because localField studentId and foreignField _id are of different type. So, is there some way studentId be converted/compared to the _id so that a join operation could be performed?

2
mongodb
You cannot. It's not possible to cast values in the aggregation framework. You need to fix the data that is presently a string and make them ObjectId values instead, so the local and foreign keys actually match. – Neil Lunn

2 Answers

0
votes

intent this :

college(studentId)  --- > student (_id)
 string             ---->     ObjectId


we need to convert to ObjectId

    db.college.aggregate([
     {
 "$addFields": {
    "newStudentId" :  { "$toObjectId": "$studentId" }
       }
    },{
        $lookup: {
           from: "student",
           localField: "newStudentId",
           foreignField: "_id",
           as: "Related"
        }
    }])
0
votes

If you have upgraded to mongodb 4.0. They have added the feature of converting object id to string by "_id.str" . So your overall query could be like

db.college.aggregate(
{
    $lookup: {
       from: "student",
       localField: "studentId",
       foreignField: "_id.str",
       as: "Related"
    }
})

This should work well.