MongoDB aggregation lookup to compare a string and ObjectId field easily

If you are looking for a solution to aggregate a lookup between string and ObjectId, look no further.

Let’s say you have the following collections.

Now you want to expand the “apartments” field by the collection entry. Usually you would do this:

db.getCollection("buildings").aggregate([
 {
     $lookup: {
         from: "apartments",
         localField: "apartments._id",
         foreignField: "_id",
         as: "apartments"
     }
 }
])

Your result will mostly be this:

{ 
 ...
  "apartments" : [  ], 
 ...
}

The “apartments” field is empty, because MongoDB in most versions can not compare string and ObjectId correctly.

How to fix this problem without changing your data or bad performance of “unwinds”

Most solutions in the web will use $unwind and then $addField, but this is very time consuming when you have a large amount of documents.

Here is a very easy solution for that: use $addField with $map.

[{
    $addFields: {
      "apartments": {
        $map: {
          input: "$apartments",
          in: {
            $toObjectId: "$$this._id" 
          }
        }
      }
    }
},
{
     $lookup: {
         from: "apartments",
         localField: "apartments",
         foreignField: "_id",
         as: "apartments"
     }
 }
]

After running the query above, your results will look like how you would expect it:

{ ...
  "title" : "Pearl Court", 
  "apartments" : 
      [ 
        { 
            "_id" : ObjectId("6425695ba69a66001b667dc5"), 
            "title" : "Unit 007", 
            "description" : "Lorem ipsum..." 
            ...
        } 
      ],   
  ...
}

Hope I helped you save a lot of time looking for a solution to expand your collections.

Thanks for reading,

Thoren