Trying to use this $lookup query in mongo DB.

db.request_user.aggregate( [
   {
      $lookup:
         {
           from: 'request',
           let: {req_id: "$requestId",curr_user:"$user"},
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         {$eq: [ "$requestId","$$req_id"]}
                         {$eq: [ "$currentUser","$$curr_user"]}
                       ]
                    }
                 }
              },
           ],
           as: "result"
         }
    }
] )

I am getting this error:

{
    "message" : "arguments to $lookup must be strings, let: { req_id: '$requestId' } is type object",
    "ok" : 0,
    "code" : 4570,
    "codeName" : "Location4570"
}

Found some sources saying let is not compatible with mongoDB 3 ~ versions. I am using version 3.4. If it's true.. can some please suggest an alternative.

CodePudding user response：

As I mentioned in the comments this $lookup syntax is only available starting version 3.6. The alternative would be to use the "old" $lookup syntax which allows a join only on a single field. Then to add additional filtering based on the other condition, like so:

db.request_user.aggregate( [
    {
        $lookup:
            {
                from: 'request',
                localField: "user",
                foreignField: "currentUser",
                as: "result"
            }
    },
    {
        $addFields: {
            result: {
                $filter: {
                    input: "$result",
                    cond: {$eq: ["$$this.requestId", "$requestId"]}
                }
            }
        }
    }
] )

It would be better if you use the field that will match less documents in the original $lookup and the other field in the $filter expression, only you can know the distribution based on your data.