I have two collections, Product and Stock

Below are example values

• Product

{
    "_id" : ObjectId("63513c705f31b4bcb75b80ce"),
    "name" : "Coca-cola",    
    "stocks" : [
        ObjectId("63513c705f31b4bcb75b80d0")
        ObjectId("63513c705f31b4bcb75b80d1")
    ]
}

• Stock

[{
    "_id" : ObjectId("63513c705f31b4bcb75b80d0"),
    "count" : 9,
    "remaining" : 6,
    "costPerItem" : 10,
    "createdAt" : ISODate("2022-10-20T12:17:52.985 0000"),
},
{
    "_id" : ObjectId("63513c705f31b4bcb75b80d1"),
    "count" : 10,
    "remaining" : 3,
    "costPerItem" : 10,
    "createdAt" : ISODate("2022-10-20T12:17:52.985 0000"),
}]

How do I query products whose sum of remaining stock (remaining field of stocks) is less than for example 100?

CodePudding user response：

One option is to use:

1. $lookup with pipeline to get all the remaining stock count per stock
2. Sum it up using $sum
3. $match the relevant products

db.products.aggregate([
  {$lookup: {
      from: "stock",
      let: {stocks: "$stocks"},
      pipeline: [
        {$match: {$expr: {$in: ["$_id", "$$stocks"]}}},
        {$project: {_id: 0, remaining: 1}}
      ],
      as: "remaining"
  }},
  {$set: {remaining: {$sum: "$remaining.remaining"}}},
  {$match: {remaining: {$lt: 100}}}
])

See how it works on the playground example