MongoDB query aggregation localField ObjectId string and foreign field ObjectId

Question

My goal is to aggregate 2 collections where in one, namely the user collection, I have an array of string type objectid that represent the actual ids in the other collection, namely the images collection. im using the following code

    await dbConn()
    return User.aggregate([
       {
           $match:{_id: mongoose.Types.ObjectId(id)}
       },
       {
           $unwind:'$imgIds'
       },

       {
           $lookup:{
               from:'images',
               localField:'imgIds',
               foreignField:'_id',
               as:'imgObjs'
           }
       }
    ],(err,res)=>console.log(res))
}

so there's a match of one item in the users collection. then deconstructing the imgIds array and trying to make the connection between the two with lookup whereas localField is a string and _id is an ObjectId. The result is ImgObjs is an empty array.

Any thoughts about this one? Im using version Mongo 3.6.12

------------EDIT--------------

I found out something very strange in my server behavior. When im doing a simple findById call, my image item is an array of ObjectId. But when using aggregate, the ObjecIds become String. Anyone has any idea whats up here?

{ "_id": { "$oid": "5e86209dd907804180cb4e11" }, "title": "flowers", "url": "https://res.cloudinary.com/dppogsm2u/image/upload/v1585763607/sample.jpg", "user": "5e86216947d8db4d9880ba9b", "__v": 0 }{ "_id": { "$oid": "5e86216947d8db4d9880ba9b" }, "following": [], "followers": [], "imgIds": [ "5e86209dd907804180cb4e12" ], "name": "Josh", "email": "[email protected]", "password": "123456", "__v": 0 } — Danny Boris Ov
@DannyBorisOv : Either you've to upgrade to newer versions at least to 4.0 or latest 4.2. Or you need to convert all those strings to ObjectId() in code & update all docs then you'll be able to do your $lookup — whoami - fakeFaceTrueSoul

abondoa abondoa · Accepted Answer · 2020-04-03T07:49:43

You need to convert your strings to object IDs (or your object IDs to string).

For Mongo version 4.0 or higher

You can use $toObjectId to do this. In your case you can add an $addFields stage after your unwind:

{
  $addFields: {
    imgIdObjId: { $toObjectId: $imgIds }
  }
}

In your lookup you will need to use that field instead of imgIds:

{
  $lookup:{
    from:'images',
    localField:'imgIdObjId',
    foreignField:'_id',
    as:'imgObjs'
  }
}

For reference checkout: https://docs.mongodb.com/manual/reference/operator/aggregation/toObjectId/

MongoDB query aggregation localField ObjectId string and foreign field ObjectId

1 Answers