MongoDB Aggregation: How to find cost of remaining stocks

Question

I want to find out how much cost I have in remaining stocks. It should filter out stock with remaining = 0

I have the following data

db={
  stocks: [
    {
      remaining: 2,
      costPerItem: 5
    },
    {
      remaining: 3,
      costPerItem: 2
    },
    {
      remaining: 0,
      costPerItem: 3
    }
  ]
}

I have created Mongo Playground: https://mongoplayground.net/p/d5S19t60V4B

Total cash of each item is remaining * costPerItem

The expected result should return total cash in stock: 16.

Answer 1

Group all documents, and sum the total of the result multiplying the remaining and costPerItem values.

db.stocks.aggregate([
  {
    $group: {
      _id: null,
      totalCashInStock: {
        $sum: {
          $multiply: [
            "$remaining",
            "$costPerItem"
          ]
        }
      }
    }
  }
])

Demo @ Mongo Playground

You could add a $match stage before the $group stage to filter the document with remaining not 0. It is optional as if remaining is 0, the outcome after multiplying with costPerItem will be 0, so technically the result will be the same as the aggregation query without the $match stage.

Updated: In case there is a document with negative remaining value, then the $match stage is required for the accurate calculation.