Filter in a MongoDB lookup collection

Question

I want to query such that:

Lock.Key == Queue.Data.Key
Queue.Result == "Error"

• Collection Lock has a key Key type ObjectId
• Collection Queue has a key Data.Key type ObjectId
• Collection Queue has a key Result type String

Where Key is unique, and Queue.Result = Error does exist.

I have seen many examples and tried them all but non worked so far. Below is one of the examples I tried:

db.getCollection("Lock").aggregate([
      {
      $lookup :
        {
           from : "Queue",
           let : {key : "Data.Key",
                  result : "Result"},
           pipeline :[
             {
                 $match : {
                     $expr : {
                         $and : [
                           {$eq: ["$$key", "Key"] },
                           {$eq: ["$$result" , "Error"] }
                         ]
                     }
                 }
             }],
           as : "queue"
        }  
  }

I had also tried with adding $project and $unwind to the pipline but that did also not work.

What is the way to get this done?

Answer 1

You can use the below query to get the expected result, https://mongoplayground.net/p/m87fFTPsGrk

db.Lock.aggregate([
  {
    $lookup: {
      from: "Queue",
      let: {
        lockKey: "$Key"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$Data.Key",
                    "$$lockKey"
                  ]
                },
                {
                  $eq: [
                    "$Result",
                    "Error"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "Queues"
    }
  }
])

this was under the assumption that you have similar data set as below.

{
  "Lock": [
    {
      "_id": 1,
      "Key": "one",
      
    },
    {
      "_id": 2,
      "Key": "two"
    }
  ],
  "Queue": [
    {
      "_id": 1,
      "Data": {
        "Key": "one"
      },
      "Result": "Success"
    },
    {
      "_id": 2,
      "Data": {
        "Key": "one"
      },
      "Result": "Error"
    },
    {
      "_id": 3,
      "Data": {
        "Key": "two"
      },
      "Result": "Error"
    }
  ]
}