How to fetch related user info when fetching all posts in MongoDB

Question

Hello I am new to MongoDB. I want to fetch all posts with user details of who created the post. I have two collections.

users collection:

[{
   "_id": {
     "$oid": "655d897b794e33f8ae0e2377"
   },
   "name": "kunal",
 },
 {
   "_id": {
     "$oid": "655d897b794e33f8ae0e237b"
   },
   "name": "Amit",
 }]

user_posts collection

[{
    "_id": {
      "$oid": "655d897b794e33f8ae0e2378"
    },
    "user_id": "655d897b794e33f8ae0e2377",
    "post_name": "Test Post Group",
  },
  {
    "_id": {
      "$oid": "655d897b794e33f8ae0e2379"
    },
    "user_id": "655d897b794e33f8ae0e2377",
    "post_name": "Test Post Group",
  },
  {
    "_id": {
      "$oid": "655d897b794e33f8ae0e237a"
    },
    "user_id": "655d897b794e33f8ae0e2377",
    "post_name": "Test Post 3",

  },
  {
    "_id": {
      "$oid": "655d897b794e33f8ae0e237c"
    },
    "user_id": "655d897b794e33f8ae0e237b",
    "post_name": "Test Post Amit",
  }]

In the users collection, _id is an ObjectId but in the user_posts collection, user_id is not an ObjectId. But I don't want to make user_id as ObjectId.

I tried below query:

db.user_posts.aggregate([
  {
    '$lookup' : {
      'as' : 'user_info',
      'from' : 'users',
      'foreignField' : '_id',
      'localField' : 'user_id'
    }
  }
]).pretty();

Output:

[
  {
    "_id": ObjectId("655c3285822c4a52150c30a8"),
    "post_name": "Test Post",
    "user_id": "655c3285822c4a52150c30a7",
    "user_info": []
  },
  {
    "_id": ObjectId("655c3285822c4a52150c30a9"),
    "post_name": "Test Post 2",
    "user_id": "655c3285822c4a52150c30a7",
    "user_info": []
  },
  {
    "_id": ObjectId("655c3285822c4a52150c30aa"),
    "post_name": "Test Post 3",
    "user_id": "655c3285822c4a52150c30a7",
    "user_info": []
  }
]

This above query only works if makes user_id as an ObjectId at the schema level. May be needed to convert user_id to an ObjectId in the run-time query itself. Can anyone help me to solve this issue?

https://mongoplayground.net/p/qPQGN2VCH8-

Answer 1

You must ensure that the values must be in the same type for matching/comparison. Reference: Join Conditions and Subqueries on a Joined Collection

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

Demo @ Mongo Playgrund