Mongodb Join on _id field from String to ObjectId

You can use $toObjectId aggregation from mongodb 4.0 which converts String id to ObjectId

db.role.aggregate([  { "$lookup": {    "from": "user",    "let": { "userId": "$_id" },    "pipeline": [      { "$addFields": { "userId": { "$toObjectId": "$userId" }}},      { "$match": { "$expr": { "$eq": [ "$userId", "$$userId" ] } } }    ],    "as": "output"  }}])

Or you can use $toString aggregation from mongodb 4.0 which converts ObjectId to String

db.role.aggregate([  { "$addFields": { "userId": { "$toString": "$_id" }}},  { "$lookup": {    "from": "user",    "localField": "userId",    "foreignField": "userId",    "as": "output"  }}])

This is not possible as of MongoDB 3.4. This feature has already been requested, but hasn't been implemented yet. Here are the corresponding tickets:

• SERVER-22781: Allow $lookup between ObjectId (_id.str) andstring
• SERVER-24947: Need a type conversion mechanism for booleans,ISODates, ObjectID

For now you'll have to store userId as ObjectId

EDIT

The previous tickets were fixed in MongoDB 4.0. You can now achieve this with the folowing query:

db.user.aggregate([  {    "$project": {      "_id": {        "$toString": "$_id"      }    }  },  {    "$lookup": {      "from": "role",      "localField": "_id",      "foreignField": "userId",      "as": "role"    }  }])

result:

[  {    "_id": "584aac38686860d502929b8b",    "role": [      {        "_id": ObjectId("584aaca6686860d502929b8d"),        "role": "Admin",        "userId": "584aac38686860d502929b8b"      }    ]  }]

try it online: mongoplayground.net/p/JoLPVIb1OLS

I think the previous answer has an error on the $toObjectId case. The let statement applies to the db collection on which the function aggregate is called (i.e 'role') and not on the collection pointed by "from" (i.e 'user').

db.role.aggregate([  { "$lookup": {    "let": { "userObjId": { "$toObjectId": "$userId" } },    "from": "user",    "pipeline": [      { "$match": { "$expr": { "$eq": [ "$_id", "$$userObjId" ] } } }    ],    "as": "userDetails"  }}])

Or

db.role.aggregate([  { "$project": { "userObjId": { "$toObjectId": "$userId" } } },  { "$lookup": {    "localField": "userObjId",    "from": "user",    "foreignField": "$_id",    "as": "userDetails"  }}])

And

db.user.aggregate([  { "$project": { "userStrId": { "$toString": "$_id" }}},  { "$lookup": {    "localField": "userStrId",    "from": "role",    "foreignField": "userId",    "as": "roleDetails"  }}])