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?

CodePudding user response：

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

CodePudding user response：

Query

• match
• lookup on id/item_id, and match branch with "main" (if it doesn't exists it will be false anyways)
• keep only the not empty

*query is almost the same as @YuTing one,but i had written it anyways, so i send it, for the small difference of alternative lookup syntax

Test code here

items.aggregate(
[{"$match":
  {"$expr":
   {"$and":
    [{"$eq":["$status", "active"]},
     {"$ne":[{"$type":"$name"}, "missing"]}]}}},
 {"$lookup":
  {"from":"inventory",
   "localField":"id",
   "foreignField":"item_id",
   "pipeline":[{"$match":{"$expr":{"$eq":["$branch", "main"]}}}],
   "as":"inventory"}},
 {"$match":{"$expr":{"$ne":["$inventory", []]}}},
 {"$unset":["inventory"]}])