Mongodb Join on _id field from String to ObjectId

Question

I have two collections

1. User

   {    "_id" : ObjectId("584aac38686860d502929b8b"),    "name" : "John" } 

2. Role

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

I want to join these collection based on the userId (in role collection) - _id ( in user collection).

I tried the below query:

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

This gives me expected results as long as i store userId as a ObjectId. When my userId is a string there are no results. Ps: I tried

foreignField: '_id'.valueOf()

and

foreignField: '_id'.toString()

. But no luck to match/join based on a ObjectId-string fields.

Any help will be appreciated.

Answer 1

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"   }} ]) 

Answer 2

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) and string
• 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