Mongodb calculate product stock from sales and purchase

Question

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. I have created a 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.

What is what you want to achieve? Could you add an expected output? — Rubén Vega, Jul 07 '23 at 11:07
So you want your initial stock minus sales plus purchased right? — Rubén Vega, Jul 10 '23 at 13:23

score 0 · Answer 1 · answered Jul 11 '23 at 09:25

First you need to join your 3 collections, using 2 lookup with some pipelines to unwind the sales and purchases collection. The reason for this is that we only need to merge the individual products and not the whole sale.

Once this is done, we would apply sum and subtract to the joined arrays and the initial field.

Mongoplayground example

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
    }
  }
])

Mongodb calculate product stock from sales and purchase

1 Answers1