Calculate product stock from sales and purchase

Working with Data
1

I was trying to build simple billing application. I have store product opening quantity, and sales and purchases along with product id. I need product wise current stock of all products on Products collection. Playground

Products collection

[
    {
      "_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 Collection

[
    {
      "_id": ObjectId("64a559f68d79acbc66d96fdf"),
      "products": [
        {
          "product": ObjectId("6499d7eb72fb2552774c9f80"),
          "qty": 3
        },
        {
          "product": ObjectId("6499d83d72fb2552774c9f84"),
          "qty": 3
        },
        
      ]
    },
    {
      "_id": ObjectId("64a559da8d79acbc66d96fde"),
      "products": [
        {
          "product": ObjectId("6499d7eb72fb2552774c9f80"),
          "qty": 3
        },
        {
          "product": ObjectId("6499d83d72fb2552774c9f84"),
          "qty": 1.5
        },
        
      ]
    }
  ]

Purchase Collection

[
    {
      "_id": ObjectId("64a5b540ffcbb3b942ccaae8"),
      "products": [
        {
          "product": ObjectId("6499d81a72fb2552774c9f82"),
          "qty": 2
        },
        {
          "product": ObjectId("6499d7eb72fb2552774c9f80"),
          "qty": 3.3
        }
      ]
    }
  ]

My expected result looks like bellow.

[
  {
    "_id": ObjectId("6499d7eb72fb2552774c9f80"),
    "name": "Product 1",
    "stock": 7.3
  },
  {
    "_id": ObjectId("6499d81a72fb2552774c9f82"),
    "name": "Product 2",
    "stock": 12
  },
  {
    "_id": ObjectId("6499d83d72fb2552774c9f84"),
    "name": "Product 3",
    "stock": 15.5
  },
  {
    "_id": ObjectId("6499d86e72fb2552774c9f86"),
    "name": "Product 4",
    "stock": 15
  }
]

Please help me out. Thank you.

2

You are using data in a bit of a relational way here. Why mot keep the product collection updated as you go?
Other than thaat use lookup, unwind and group to summarise it.

Actually can probably just do lookups and then a reduce…

If you draw a blank i can try and setup an example tomorrow.

3

Had a play, something like this?

db.getCollection("Product").aggregate([
{
    $lookup:{
        from:'Purchases',
        localField:'name',
        foreignField:'name',
        as :'Purchases'
    }
},
{
    $lookup:{
        from:'Sales',
        localField:'name',
        foreignField:'name',
        as :'Sales'
    }
},
{
    $project:{
        currentQty:{
            $subtract:[
                {$sum:'$Purchases.qty'},
                {$sum:'$Sales.qty'}
            ]
        }
    }
}
])

Obviously ensure appropriate indexes are available for the lookups.