Mongodb. Aggregate sum / Group rows by field and get sum per row / Get total field amount per user-CodePudding

I have payments table. Every row has a field user (id) and amount.

I need to get total spent amount per every user.

Example of payment row:

{
    "_id" : ObjectId("60ddedd27e6b7be96409257b"),
    "user" : ObjectId("60dded257e6b7be964092573"),
    "provider" : "connectum",
    "id" : "62256402926432367",
    "status" : "new",
    "amount" : "94.00",
    "currency" : "US",
    "extra" : {
        "real_gift" : ObjectId("60d1f2e580efe489fe150d75"),
        "user_id" : 245672982
    },
    "created_at" : ISODate("2021-07-01T16:31:14.000Z")
}

I tried (as described here) and looked this docs:

db.getCollection('payments').aggregate([{"$group" : {_id : "$user", "my_field" : {$sum: "$amount"}}}])

But every time my_field is 0, but surely this is impossible by app payment restrictions

P.S. Additionally I need to check if user are still registered in a system (no deleted an account).

So I have a table users with last_visit column, but don't know how to combine this 2 queries.

CodePudding user response：

Your amount field is an string, you have to parse to double to can sum the values. You can use $toDouble like this:

db.collection.aggregate([
  {
    "$group": {
      _id: "$user",
      "my_field": {
        $sum: {
          "$toDouble": "$amount"
        }
      }
    }
  }
])

Check this example