MongoDB Join on _id field from String to ObjectId aggregate $lookup

Question

I have two collections.

Customers:

{
   "_id" : ObjectId("584aac38686860d502929b8b"),
   "name" : "user",
   "email" : "[email protected]"
}

Posts:

{
   "_id" : ObjectId("584aaca6686860d502929b8d"),
   "title" : "Post",
   "description":"description",
   "user_id" : "584aac38686860d502929b8b"  
}

I want to join this collection based on the user_id (from posts collection) - _id ( in customers collection).

I tried the below query:

dbo.collection('customers').aggregate([
        {
            $lookup:
                {
                    from: 'posts',
                    localField: 'user_id',
                    foreignField: '_id',
                    as: 'posts'
                }
        }
    ])

but it's not working.

The output I am getting:

{
    "_id": "584aac38686860d502929b8b",
    "name": "user",
    "email": "[email protected]",
    "posts": []
}

Answer 1

From attached posts collection, user_id was a string but not ObjectId.

To compare, you have to convert user_id to ObjectId first.

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

Sample Mongo Playground

---

Note: From your existing $lookup, you have reversed localField and foreignField.

Equality Match with a Single Join Condition

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}