db={products:[{"_id":ObjectId("6499d7eb72fb2552774c9f80"),"name":"Product 1","openingStock":10},{"_id":ObjectId("6499d81a72fb2552774c9f82"),"name":"Product 2","openingStock":10},{"_id":ObjectId("6499d83d72fb2552774c9f84"),"name":"Product 3","openingStock":20},{"_id":ObjectId("6499d86e72fb2552774c9f86"),"name":"Product 4","openingStock":15},],sales:[{"_id":ObjectId("64a559f68d79acbc66d96fdf"),"products":[{"product":ObjectId("6499d7eb72fb2552774c9f80"),"qty":1},{"product":ObjectId("6499d83d72fb2552774c9f84"),"qty":2},]},{"_id":ObjectId("64a559da8d79acbc66d96fde"),"products":[{"product":ObjectId("6499d7eb72fb2552774c9f80"),"qty":3},{"product":ObjectId("6499d83d72fb2552774c9f84"),"qty":1.5},]}],purchase:[{"_id":ObjectId("64a5b540ffcbb3b942ccaae8"),"products":[{"product":ObjectId("6499d81a72fb2552774c9f82"),"qty":2},{"product":ObjectId("6499d7eb72fb2552774c9f80"),"qty":3.3}]}]}

db.products.aggregate([{"$lookup":{"from":"sales","let":{"id":"$_id"},"pipeline":[{"$unwind":"$products"},{"$match":{$expr:{"$eq":["$products.product","$$id"]}}}],"as":"sales"}},{"$lookup":{"from":"purchase","let":{"id":"$_id"},"pipeline":[{"$unwind":"$products"},{"$match":{$expr:{"$eq":["$products.product","$$id"]}}}],"as":"purchases"}},{"$addFields":{"finalStock":{$sum:[{"$subtract":["$openingStock",{"$sum":["$sales.products.qty",]}]},{"$sum":["$purchases.products.qty",]}]}}},{"$project":{_id:1,name:1,finalStock:1}}])