I have two collections:

• cats
• balls

"cats" collection has documents with key "ballId" of type string

"balls" collection has documents with key "_id" of type ObjectId

An $lookup inside an aggregation is able to retrieve results if the join is done on keys with the same data type. However in my case "ballId" and "_id" are of different types. This code retrieves the cats but doesn't retrieve the related balls:

collection('cats').aggregate([
  { $match:{} },
  {
    $lookup: {
      from: "balls",
      localField: "ballId",
      foreignField: "_id",
      as: "balls"
  }
}
]);

How can I use $lookup with lossy data type?

CodePudding user response：

Use $lookup with pipeline stage.

Join both collections by converting balls' _id to string ($toString) and next compare both values as string ($eq).

db.cats.aggregate([
  {
    $match: {}
  },
  {
    $lookup: {
      from: "balls",
      let: {
        ballId: "$ballId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                {
                  "$toString": "$_id"
                },
                "$$ballId"
              ]
            },
            
          }
        }
      ],
      as: "balls"
    }
  }
])

Sample Mongo Playground