MongoDB aggregation matching ObjectId against string

Question

I have the following document which is also available in the mongo playground at: https://mongoplayground.net/p/zhcoi1BF0Ny

db={
  MyCollectionOne: [
    {
      "firstId": "10",
      "secondId": "123456789012345678901234"
    },
    {
      "firstId": "11",
      "secondId": "999999999999999999999999"
    }
  ],
  MyCollectionTwo: [
    {
      "_id": ObjectId("123456789012345678901234"),
      "otherFieldOne": "Some Data",
      "otherFieldTwo": [
        {
          someNumber: 7
        }
      ]
    },
    {
      "_id": ObjectId("999999999999999999999999"),
      "otherFieldOne": "Some Other Data",
      "otherFieldTwo": [
        {
          someNumber: 9
        },
        {
          someNumber: 39
        }
      ]
    }
  ]
}

Given a firstId, I need to determine the correct MyCollectionTwo entry to return. So for example, if I was given a firstId of 11, I would use that to lookup its corresponding secondId (which is "999999999999999999999999"). I would need to convert the secondId value to an ObjectId and then look through the MyCollectionTwo _id fields until I find the matching one.

I gave it a try and am very close but cannot figure out how to correctly do the string->objectId conversion.

db.MyCollectionTwo.aggregate([
  {
    $lookup: {
      from: "MyCollectionOne",
      localField: "_id",
      foreignField: "secondId",
      as: "Temp"
    }
  },
  {
    $unwind: "$Temp"
  },
  {
    $match: {
      "Temp.firstId": "11"
    }
  },
  {
    $project: {
      _id: 1,
      otherFieldOne: 1,
      otherFieldTwo: 1
    }
  }
]).find()

I am aware there is a let/pipeline which can use a $toObjectId but I can't get that working in the above context.

Any help would be appreciated. Thanks!

Answer 1

You can try this

db.MyCollectionTwo.aggregate([
  {
    $lookup: {
      //searching collection name
      from: "MyCollectionOne",
      //setting variable [searchId] where your string converted to ObjectId
      let: {
        "searchId": {
          $toObjectId: "$secondId"
        }
      },
      //search query with our [searchId] value
      "pipeline": [
        //searching [searchId] value equals your field [_id]
        {
          "$match": {
            "$expr": [
              {
                "_id": "$$searchId"
              }
            ]
          }
        },
        
      ],
      as: "Temp"
    }
  },
  {
    $unwind: "$Temp"
  },
  {
    $match: {
      "Temp.firstId": "11"
    }
  },
  {
    $project: {
      _id: 1,
      otherFieldOne: 1,
      otherFieldTwo: 1
    }
  }
]).find()

https://mongoplayground.net/p/es0j0AiPDCj