CODEWITHSUNDEEP
mongodbjoinaggregation-frameworkmongodb-lookup
Alok
Alok

Reputation: 10534

Auto conversion of foreign key from string to ObjectId to make $lookup aggregation work by MongoDB

I have two collections and I am using one of collections _id field as foreign key in other collection. But _id is Object and in in other collection it is string so $lookup is not able to match.

Collection 1: User collection

{ "_id" : ObjectId("6471bf9db77f0b33a9b0bd38"), "name" : "Alok", "city" : "someCity" }

Collection 2: Order collection

{ "_id" : ObjectId("6471bfd4b77f0b33a9b0bd39"), "uid" : "6471bf9db77f0b33a9b0bd38", "product" : "product1" }

I am trying to use $lookup

db.order.aggregate([{$lookup: {from: 'users', localField:'uid', foreignField:'_id', as:'join'}}])

This $lookup does not work because _id is Object and uid is string.

This is common requirement so there must be some automated way as I dont want to use $lookup with long pipeline.

What can quickest fix to make this $lookup work without using long pipeline?

Upvotes: 0

Views: 442

Answers (1)

Yong Shun
Yong Shun

Reputation: 51125

As mentioned in the comment, you need a $set stage to convert the uid to ObjectId type before $lookup stage.

db.order.aggregate([
  {
    $set: {
      uid: {
        $toObjectId: "$uid"
      }
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "uid",
      foreignField: "_id",
      as: "join"
    }
  }
])

Otherwise, you may use $lookup with pipeline as well.

db.order.aggregate([
  {
    $lookup: {
      from: "users",
      let: {
        uid: {
          $toObjectId: "$uid"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$uid"
              ]
            }
          }
        }
      ],
      as: "join"
    }
  }
])

Upvotes: 1

Related Questions