Aggregate Lookup with pipeline and match not working mongodb

Question

I have these 2 simple collections:

items:

{
    "id" : "111",
    "name" : "apple",
    "status" : "active"
}    
{
    "id" : "222",
    "name" : "banana",
    "status" : "active"
}

inventory:

{
    "item_id" : "111",
    "qty" : 3,
    "branch" : "main"
}
{
    "item_id" : "222",
    "qty" : 3
}

Now I want to to only return the items with "status" == "active" and with "branch" that exist and is equal to "main" in the inventory collection. I have this code below but it returns all documents, with the second document having an empty "info" array.

db.getCollection('items')
.aggregate([
  {$match:{$and:[
                {"status":'active'},
                {"name":{$exists:true}}
            ]
}},
{$lookup:{
    as:"info",
    from:"inventory",
    let:{fruitId:"$id"},
    pipeline:[
     {$match:{
         $and:[
            {$expr:{$eq:["$item_id","$$fruitId"]}},
            {"branch":{$eq:"main"}},
            {"branch":{$exists:true}}
         ]         
         }    
     }
    ]    
}}
])

Can anyone give me an idea on how to fix this?

Answer 1

Your code is doing well. I think you only need a $match stage in the last of your pipeline.

db.items.aggregate([
  {
    $match: {
      $and: [
        { "status": "active" },
        { "name": { $exists: true } }
      ]
    }
  },
  {
    $lookup: {
      as: "info",
      from: "inventory",
      let: { fruitId: "$id" },
      pipeline: [
        {
          $match: {
            $and: [
              { $expr: { $eq: [ "$item_id", "$$fruitId" ] } },
              { "branch": { $eq: "main" } },
              { "branch": { $exists: true } }
            ]
          }
        }
      ]
    }
  },
  {
    "$match": {
      "info": { "$ne": [] }
    }
  }
])

mongoplayground