CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Mark Thien
Mark Thien

Reputation: 1167

Get total amount in an array of all documents

my document in user collection is like below:

{
    "_id" : ObjectId("58ca3a856d13601f9a474dca"),
    "name" : "user A",
    "email" : "[email protected]",
    "transaction" : [ 
        {
            "amount" : 50,
            "type" : "CASH"
        }
    ]
},

{
    "_id" : ObjectId("58ca3a856d13601f9a474dcb"),
    "name" : "user B",
    "email" : "[email protected]",
    "transaction" : [ 
        {
            "amount" : 100,
            "type" : "CHEQUE"
        },
        {
            "amount" : 200,
            "type" : "CASH"
        },
        {
            "amount" : -20,
            "type" : "USED_SERVICE"
        }
    ]
},

{
    "_id" : ObjectId("58ca3a856d13601f9a474dcc"),
    "name" : "user C",
    "email" : "[email protected]",
    "transaction" : [ 
        {
            "amount" : 20,
            "type" : "CASH"
        },
        {
            "amount" : -5,
            "type" : "USED_SERVICE"
        }        
    ]
}

I need to add up all the transaction of all user with type like CASH or CHEQUE.

I tried the $sum aggregation operator but still no idea how to get it works.

https://docs.mongodb.com/manual/reference/operator/aggregation/sum/

Upvotes: 1

Views: 846

Answers (3)

Rubin Porwal
Rubin Porwal

Reputation: 3845

According to above mentioned description as a solution to it please try executing following query in MongoDB shell

db.user.aggregate(

  // Pipeline
  [
    // Stage 1
    {
      $match: {
      transaction:{$elemMatch:{type:{$in:['CHEQUE','CASH']}}}
      }
    },

    // Stage 2
    {
      $unwind: {path:'$transaction'}
    },

    // Stage 3
    {
      $group: {
       _id:{type:'$transaction.type'},
       total:{$sum:'$transaction.amount'}
      }
    }

  ]


);

Upvotes: 0

Ori Dar
Ori Dar

Reputation: 19000

db.user.aggregate(
    {$unwind: "$transaction"},
    {$match: {"transaction.type": {$in: ["CASH", "CHEQUE"]}}},
    {$group: {_id: "total", sum: {$sum: "$transaction.amount"}}}
)
  1. First unwind the transactions array
  2. Then, match by transaction type(s)
  3. Finally, group all transactions, summing their amount

Upvotes: 3

Sede
Sede

Reputation: 61225

The optimal way to do this is actually in MongoDB version 3.4 or newer. This version of mongod provides the $reduce operator which allow us to apply the $sum operator to the array of "amount" value where the "type" is "CASH" or "CHEQUE".

But first you need to compute that array by $filtering the "transaction" array and using $map array operator to return only the "amount" value.

The "cond" expression in $filter is a simple $in expression also new in version 3.4.

Of course to get the total for all documents in the collection, you need to do this in the $group stage and use null or whatever the equivalent is in your programming language as the _id value.

db.collection.aggregate([
    {
       "$group": {
          "_id": null,
          "total": {
             "$sum": {
                "$reduce": {
                   "input": {
                      "$map": {
                         "input": {
                            "$filter": {
                               "input": "$transaction",
                               "as": "t",
                               "cond": {
                                  "$in": [
                                     "$$t.type",
                                     [
                                        "CASH",
                                        "CHEQUE"
                                     ]
                                  ]
                               }
                            }
                         },
                         "as": "elt",
                         "in": "$$elt.amount"
                      }
                   },
                   "initialValue": 0,
                   "in": {
                      "$add": [
                         "$$value",
                         "$$this"
                      ]
                   }
                }
             }
          }
       }
    }
])

Another less efficient alternative if your mongod version is 3.2 is to $filter the "transaction" array using the $setIsSubset operator in the "cond" expression.

From there, you compute the sum of the "amount" value in another $project stage and return the total for all document with $sum in a final $group stage. 

db.collection.aggregate([
   {
      "$project": {
         "transaction": {
            "$filter": {
               "input": "$transaction",
               "as": "t",
               "cond": {
                  "$setIsSubset": [
                     [
                        "$$t.type"
                     ],
                     [
                        "CASH",
                        "CHEQUE"
                     ]
                  ]
               }
            }
         }
      }
   },
   {
      "$project": {
         "amount": {
            "$sum": "$transaction.amount"
         }
      }
   },
   {
      "$group": {
         "_id": null,
         "total": {
            "$sum": "$amount"
         }
      }
   }
])

Upvotes: -1

Related Questions