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

Question

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?

Before $lookup stage you need to set the `uid` field as ObjectId. [Demo](https://mongoplayground.net/p/vABHXdVa9db) — Yong Shun, May 28 '23 at 03:41
Why not be consistent and store them both as ObjectIds which avoids this problem altogether? — user20042973, May 28 '23 at 04:28
@YongShun: your comment is a good answer. you should post that as answer :) — Alok, May 28 '23 at 05:47
@user20042973: I was not aware of good practice of Mongodb. Now I am realizing to read good practice on foreign key in mongodb. — Alok, May 28 '23 at 05:50

score 1 · Answer 1 · answered May 28 '23 at 05:54

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"
    }
  }
])

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

1 Answers1