How can I combine two collections in MongoDB depending on different IDs?

user_15611043 0
Viewed 327

I want to join two collections of MongoDB. I want to join them if the _id and the creatorId matches. This is what I have done.

User.aggregate([
        {
           $lookup:
              {
                from: "traveladds",
                let: { personalId: "$_id"},
                pipeline: [
                   { $match:
                      { $expr:
                            [
                              { $eq: [ "$creatorId",  "$$personalId" ] },
                            ]
                         }
                   }
                ],
                as: "stockdata"
              }
         },
     ]).exec((err, result) => {
        if(err){
            res.send(err)
        }
        if(result){
            res.send({
                error: false, 
                data: result, 
            })
        }
    })

But when I do like this, all of the documents in traveladds-collection is stored in stockdata for all the users.

User collection:

"_id": "609bc1b9bbc4ff184194c20f",
  "homeCountry": "",
  "birthdate": "",
  "email": "",
  "password": "",
  "firstName": "",
  "lastName": "",
  "__v": 0,
  "about": "",
  "gender": "",

Traveladds collection:

"{
  "_id": "609bc1fdbbc4ff184194c210",
  "title": "",
  "destination": "",
  "description": "",
  "minbudget": ,
  "maxbudget": ,
  "startdate": "",
  "enddate": "",
  "creatorId": "609bc1b9bbc4ff184194c20f",
  "creatorName": "",
  "createdAt": "",
  "updatedAt": "",
  "__v": 0
},

And this is the result:

data": [
{
  "_id": "609bc1b9bbc4ff184194c20f",
  "homeCountry": "",
  "birthdate": "",
  "email": "",
  "password": "",
  "firstName": "",
  "lastName": "",
  "__v": 0,
  "about": "",
  "gender": "",
  "stockdata": [
    {
      "_id": "609bc1fdbbc4ff184194c210",
      "title": "",
      "destination": "",
      "description": "",
      "minbudget": ,
      "maxbudget": ,
      "startdate": "",
      "enddate": "",
      "creatorId": "609bc1b9bbc4ff184194c20f",
      "creatorName": "",
      "createdAt": "",
      "updatedAt": "",
      "__v": 0
    },
     {
      "_id": "609eb5f401c77217984e4a78",
      "title": "",
      "destination": "",
      "description": "",
      "minbudget": ,
      "maxbudget": ,
      "startdate": "",
      "enddate": "",
      "creatorId": "609e9bb378336c21581cd132",
      "creatorName": "",
      "createdAt": "",
      "updatedAt": "",
      "__v": 0
    },
     {
      "_id": "60a216cfd43c8e1e375cf0a8",
      "title": "",
      "destination": "",
      "description": "",
      "minbudget": ,
      "maxbudget": ,
      "startdate": "",
      "enddate": "",
      "creatorId": "609e39572c60480de0fd6ef1",
      "creatorName": "",
      "createdAt": "",
      "updatedAt": "",
      "__v": 0
    },
  ]
},

As you can see, even though there are creatorId != _id it shows in the stockdata. And all these three appears in all users.

Can someone provide me with any idéas what to do? Thanks in advance and have a nice day.

UPDATE: This in combination with the answer below worked!

personalId: {$toString: "$_id"}

mongodb lookup collections
1 Answers

You have written correct query. But there are some unnecessary brackets

 db.User.aggregate([
      {
        $lookup: {
          from: "traveladds",
          let: {
            personalId: "$_id"
          },
          pipeline: [
            {
              $match: {
                $expr: {
                  $eq: [
                    "$creatorId",
                    "$$personalId"
                  ]
                }
              }
            }
          ],
          as: "stockdata"
        }
      },
      
    ])

Working Mongo playground

user_79757710
Related