How to use groupby in mongo along with addFields containing strings?

user_15832980 0
Viewed 71

I have four fields Sender, Recipient, Credit, Debit in mongo collection. Of which Credit and Debit are numbers in form of strings. I need to group based on Sender, Recipient and add integer values of Credit, Debit and sort the result based on that net value of credit and debit

I have database like this

Sender   Recipient  Credit  Debit
“Mike”   “John”      “600”   “-300” 
“Alis”   “Ajay”      “300”   “-800” 
“Arun”   “Ankit”     “360”   “-300” 
“Mike”    “Cris”      “0”    “-100”

And I need result like this

Sender   Recipient  NetAmount
“Mike”    “John”        “300” 
Arun”     “Ankit”       “-60”
“Mike”    “Cris”        “-100”       
“Alis”    “Ajay”        “-500”

I tried to do this

DataModel.aggregate([
      {
        $group: {
          _id: {
            sender: '$ sender,
            recipient: '$recipient',
          },
        },
      },
      {
        $project: {
          credit: '$credit.creditAmount,
          debit: '$debit.debitAmount,
        },
      }, {
        $addFields: {
          netAmount: {
            $add: [{ $toInt: '$ credit }, { $toInt: '$ debit }],
          },
        }
      },
    ]);

But it didn't give the desired result.

mongoose aggregation-framework mongodb-query node.js mongodb
1 Answers
  • $group by Sender and Recipient, sum Credit and Debit amount after converting to integer using $toInt
  • $project to show required fields and sum Credit and Debit field using $add
  • $match to check NetAmount is not zero
  • $sort by NetAmount in descending order
db.collection.aggregate([
  {
    $group: {
      _id: {
        Sender: "$Sender",
        Recipient: "$Recipient"
      },
      Credit: { $sum: { $toInt: "$Credit" } },
      Debit: { $sum: { $toInt: "$Debit" } }
    }
  },
  {
    $project: {
      _id: 0,
      Sender: "$_id.Sender",
      Recipient: "$_id.Recipient",
      NetAmount: { $add: ["$Credit", "$Debit"] }
    }
  },
  { $match: { NetAmount: { $ne: 0 } } },
  { $sort: { NetAmount: -1 } }
])

Playground

user_89871280
Related