how to get the sum in embedded fields in mongoDB

Question

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!!!

Murugan Perumal · Answer 1 · 2018-06-18T05:32:15.953

1

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})

edited Jun 18 '18 at 05:32

answered Jun 16 '18 at 07:35

Murugan Perumal

975
5
15

It has error Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in. – Jie Jun 16 '18 at 12:50
I just added ,{allowDiskUse : true} in that same query, can you check now – Murugan Perumal Jun 18 '18 at 05:32

score 0 · Answer 2 · answered Jun 16 '18 at 01:19

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
    }
);

Rajat Mishra · Answer 3 · 2018-06-17T03:42:56.827

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.

I tried pipeline=[ { "$group" : { "_id": "$cms_prescription_counts", "total" : { "$sum" : "$cms_prescription_counts.ALPRAZOLAM"} }} ] db.pre_collection.aggregate(pipeline) but comes error — Jie, Jun 16 '18 at 12:47
The error is Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true to opt in. BTW I use pymongo — Jie, Jun 16 '18 at 13:18
Basically, you need to add { allowDiskUse : true } in the aggregate pipeline. When set on, this allow mongo to write temp files on disk. — Rajat Mishra, Jun 17 '18 at 03:44

score 0 · Answer 4 · answered Jun 16 '18 at 09:32

0

You can try:

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

answered Jun 16 '18 at 09:32

Neeraj Verma

703
6
15

how to get the sum in embedded fields in mongoDB

4 Answers4