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) 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