Expression within condition

Question

This is a follow-up question:

I want to aggregate the average of a collection only for documents with type: 'buy'. So the problem is how to apply the if condition in the sum expression and same goes for totalBuyAmount.

These are example documents:

[
  {
    _id: ObjectId("653c4c017800342a3bedb82e"),
    type: 'buy',
    item: 555,
    amount: 1,
    priceEach: 100000
  },
  {
    _id: ObjectId("653c4c017800342a3bedb830"),
    type: 'buy',
    item: 384,
    amount: 2,
    priceEach: 70000
  },
  {
    _id: ObjectId("653c4c017800342a3bedb831"),
    type: 'buy',
    item: 384,
    amount: 1,
    priceEach: 50000
  },
  {
    _id: ObjectId("653c4c017800342a3bedb831"),
    type: 'sell',
    item: 384,
    amount: -1,
    priceEach: 50000
  }
]

I need the totalBuyPrice only if type === 'buy'.

db.collection.aggregate([
  {
    $group: {
      _id: "$item",
      totalBuyPrice: {
        $sum: { // Only if type === 'buy'
          $multiply: [
            "$priceEach",
            "$amount"
          ]
        }
      },
      totalBuyAmount: // $sum $amount if type === 'buy'
      totalAmount: {
        $sum: "$amount"
      }
    }
  },
  {
    $project: {
      _id: 1,
      avg: {
        $divide: [
          "totalBuyPrice",
          "totalBuyAmount"
        ]
      },
      amount: "$totalAmount"
    }
  }
])

Answer 1

You can add $cond operator to perform calculations based on the condition.

db.collection.aggregate([
  {
    $group: {
      _id: "$item",
      totalBuyPrice: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$type",
                "buy"
              ]
            },
            {
              $multiply: [
                "$priceEach",
                "$amount"
              ]
            },
            0
          ]
        }
      },
      totalBuyAmount: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$type",
                "buy"
              ]
            },
            "$amount",
            0
          ]
        }
      },
      totalAmount: {
        $sum: "$amount"
      }
    }
  },
  {
    $project: {
      _id: 1,
      avg: {
        $divide: [
          "$totalBuyPrice",
          "$totalBuyAmount"
        ]
      },
      amount: "$totalAmount"
    }
  }
])

Demo @ Mongo Playground