Convert string to number and count sum of values in an array in mongodb

user_8369559 0
Viewed 418

I want to sum total of amount with the type string in array so I write this in mongo. any help please

Tried :

db.compte.aggregate([
 {$project:{
   account: "$auditMessages.eventMetaData.Counterparty Name",
   amount: "$auditMessages.eventMetaData.Transaction Amount",
   "total": {$sum:"$amount"}
 }}])

The result:

{
"_id" : ObjectId("5ed638e566a699750cbb1b0f"),
"account" : [
  "Mr Jalel CHAHED",
  "Mr Jalel CHAHED",
  "Mr Jalel CHAHED",
  "Mr Jalel CHAHED"
 ],
"amount" :
 [ 
  "4.32",
  "4.32",
   "1.32",
   "1.32"
 ],
"total" : 0}
aggregation-framework mongodb-query mongodb
1 Answers

You're creating amount field in $project and using it in same stage there itself which is why it's returning 0 on "total": {$sum:"$amount"}, also $sum seems to not take arrays.

Assuming your documents look like :

{
    "auditMessages": {
      eventMetaData: [
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 1,
          "Transaction Amount": "4.32"
        },
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 11,
          "Transaction Amount": "4.32"
        },
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 12,
          "Transaction Amount": "1.32"
        },
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 122,
          "Transaction Amount": "1.32"
        }
      ]
    }
  }

As auditMessages.eventMetaData is an array then when you do :

"$auditMessages.eventMetaData.Counterparty Name" or "$auditMessages.eventMetaData.Transaction Amount" then what you get is an array of values like what you see in your result as of now.

Query :

db.collection.aggregate([
    {
      $project: {
        account: { $arrayElemAt: [ "$auditMessages.eventMetaData.Counterparty Name", 0 ] }, // get name from first element in array,use only if name is same in all objects
        total: {
          $reduce: {
            input: "$auditMessages.eventMetaData.Transaction Amount", // Iterate on array
            initialValue: 0,
            in: { $trunc: [ { $add: [ "$$value", { $toDouble: "$$this" } ] }, 2 ] } // convert string to double, sum-up & truncate to 2 digits
          }
        }
      }
    }
  ])

Test : mongoplayground

Ref : aggregation

user_72376130
Related