MongoDB get SUM of fields with conditions

user_13545908 0
Viewed 173

On my backend I use mongoDB with nodejs and mongoose

I have many records in mongodb with this structure:

{
  ..fields
  type: 'out',
  user: 'id1', <--mongodb objectID,
  orderPayment: [
    {
      _id: 'id1',
      paid: true,
      paymentSum: 40
    },
    {
      _id: 'id2',
      paid: true,
      paymentSum: 60,
    },
    {
      _id: 'id3',
      paid: false,
      paymentSum: 50,
    }
  ]
},
{
..fields
type: 'in',
user: 'id1', <--mongodb objectID
orderPayment: [
  {
    _id: 'id1',
    paid: true,
    paymentSum: 10
  },
  {
    _id: 'id2',
    paid: true,
    paymentSum: 10,
  },
  {
    _id: 'id3',
    paid: false,
    paymentSum: 77,
  }
]
}

I need to group this records by 'type' and get sum with conditions. need to get sum of 'paid' records and sum of noPaid records.

for a better understanding, here is the result Ι need to get

Output is:

{
  out { <-- type field
    paid: 100, <-- sum of paid
    noPaid: 50 <-- sum of noPaid
  },
  in: { <-- type field
    paid: 20, <-- sum of paid
    noPaid: 77 <-- sum of noPaid
  }
}
mongoose node.js mongodb
2 Answers

use $cond in $group

db.collection.aggregate([
  {
    "$unwind": "$orderPayment"
  },
  {
    "$group": {
      "_id": "$type",
      "paid": {
        "$sum": {
          $cond: {
            if: { $eq: [ "$orderPayment.paid", true ] },
            then: "$orderPayment.paymentSum",
            else: 0
          }
        }
      },
      "noPaid": {
        "$sum": {
          $cond: {
            if: { $eq: [ "$orderPayment.paid", false ] },
            then: "$orderPayment.paymentSum",
            else: 0
          }
        }
      }
    }
  }
])

mongoplayground

user_166952450

Different solution would be this one. It may give better performance than solution of @YuTing:

db.collection.aggregate([
  {
    $project: {
      type: 1,
      paid: {
        $filter: {
          input: "$orderPayment",
          cond: "$$this.paid"
        }
      },
      noPaid: {
        $filter: {
          input: "$orderPayment",
          cond: { $not: "$$this.paid" }
        }
      }
    }
  },
  {
    $set: {
      paid: { $sum: "$paid.paymentSum" },
      noPaid: { $sum: "$noPaid.paymentSum" }
    }
  },
  {
    $group: {
      _id: "$type",
      paid: { $sum: "$paid" },
      noPaid: { $sum: "$noPaid" }
    }
  }
])

Mongo Playground

user_30272660
Related