CODEWITHSUNDEEP
mongodb
Jie
Jie

Reputation: 131

how to get the sum in embedded fields in mongoDB

I have a mongoDB collection looks like this:

{u'_id': ObjectId('5b243611dba907285af051ee'),
u'cms_prescription_counts': {u'ABILIFY': 11,
                          u'ALLOPURINOL': 86,
                          u'ALPRAZOLAM': 45,
                          u'AMLODIPINE BESYLATE': 175,
                          u'AMLODIPINE BESYLATE-BENAZEPRIL': 12,
                          u'ATENOLOL': 62,
                          u'ATENOLOL-CHLORTHALIDONE': 53,
                          u'ATORVASTATIN CALCIUM': 19,
                          u'AZITHROMYCIN': 18}},
 {u'_id': ObjectId('5b243611dba90728sad51ee'),
  u'cms_prescription_counts': {u'ABILIFY': 11,
                          u'ALLOPURINOL': 70,
                          u'ALPRAZOLAM': 20,
                          u'AMLODIPINE BESYLATE': 15,
                          u'AMLODIPINE BESYLATE-BENAZEPRIL': 24,
                          u'ATENOLOL': 62,
                          u'ATENOLOL-CHLORTHALIDONE': 53,
                          u'ATORVASTATIN CALCIUM': 19,
                          u'AZITHROMYCIN': 18}
...
...

So I want to get the total amount of 'ALPRAZOLAM' across the collection. I tried $group as follow:

{"$group": {
        "_id": "cms_prescription_counts", 
        "total": {"$sum": "I don't know what to enter here" } 
    }}

Anyone can help me? Thanks!!!

Upvotes: 0

Views: 560

Answers (4)

Murugan Perumal
Murugan Perumal

Reputation: 985

Simple, Just access embedded field using . notation in aggregation pipeline

db.collection.aggregate([  
{ "$group" : { 
       "_id": "$cms_prescription_counts", 
      "total" : { "$sum" : "$cms_prescription_counts.ALPRAZOLAM"}
}}
],{allowDiskUse : true})

Upvotes: 1

Rajat Mishra
Rajat Mishra

Reputation: 3770

You can try to aggregate on the column name :

MongoDB Enterprise > db.test.find().pretty()
{
        "_id" : ObjectId("5b243611dba907285af051ee"),
        "cms_prescription_counts" : {
                "ABILIFY" : 11,
                "ALLOPURINOL" : 86,
                "ALPRAZOLAM" : 45,
                "AMLODIPINE BESYLATE" : 175,
                "AMLODIPINE BESYLATE-BENAZEPRIL" : 12,
                "ATENOLOL" : 62,
                "ATENOLOL-CHLORTHALIDONE" : 53,
                "ATORVASTATIN CALCIUM" : 19,
                "AZITHROMYCIN" : 18
        }
}
{
        "_id" : ObjectId("5b24658a853b142da9b7bd20"),
        "cms_prescription_counts" : {
                "ABILIFY" : 11,
                "ALLOPURINOL" : 70,
                "ALPRAZOLAM" : 20,
                "AMLODIPINE BESYLATE" : 15,
                "AMLODIPINE BESYLATE-BENAZEPRIL" : 24,
                "ATENOLOL" : 62,
                "ATENOLOL-CHLORTHALIDONE" : 53,
                "ATORVASTATIN CALCIUM" : 19,
                "AZITHROMYCIN" : 18
        }
}
MongoDB Enterprise > db.test.aggregate([  { $group : { "_id": "cms_prescription_counts" , "total" : { $sum : "$cms_prescription_counts.ALPRAZOLAM"}}}], { allowDiskUse: true })
{ "_id" : "cms_prescription_counts", "total" : 65 }
MongoDB Enterprise > db.test.aggregate([  { $group : { "_id": "cms_prescription_counts" , "total" : { $sum : "$cms_prescription_counts.ABILIFY"}}}],{ allowDiskUse: true })
{ "_id" : "cms_prescription_counts", "total" : 22 }

You need to add a a parameter {allowDiskUse : true}. You can read more about it here.

For using with pymongo, you can refer this question.

Upvotes: 0

Neeraj Verma
Neeraj Verma

Reputation: 771

You can try:

db.collection.aggregate([
{$project : {'ALPRAZOLAM' : '$cms_prescription_counts.ALPRAZOLAM'}},
{$group:{_id:null,'total' : {$sum : '$ALPRAZOLAM'}}}
])

Upvotes: 0

Mạnh Quyết Nguyễn
Mạnh Quyết Nguyễn

Reputation: 18235

To sum all ALPRAZOLAM values, you can use mapReduce function:

db.yourCollection.mapReduce(
    function() {
        emit(this.cms_prescription_counts.ALPRAZOLAM); // Mapping each matching document with this item
    },
    function(keyCustId, valuesPrices) {
        return Array.sum(valuesPrices); // Sum all the values
    };
    {
         query: {}, // Filter the collection. Add your condition here
         out: "total" //  This is variable hold the total value
    }
);

Upvotes: 0

Related Questions