Mongodb Join on _id field from String to ObjectId

Kavya Mugali picture Kavya Mugali · Dec 12, 2016 · Viewed 32.7k times · Source

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

Ashh picture Ashh · Jul 11, 2018

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