Conditional expressions

Working with Data Node.js Frameworks
,
1

I want to aggregate the average of a collection only for documents with type ‘buy’.
So the problem is how to get the if condition around the sum expression and same for totalBuyAmount. Check the comments in the code :slight_smile:

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 to 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"
    }
  }
])
2

Looks like what you need is $cond.

Example:

"totalBuyAmount" : { "$sum" : { "$cond" :  {
    "$if" : { "$eq" : [ "$type" , "buy" ] } ,
    "$then" : "$amount" ,
    "$else" : 0
} } }