Mongo Aggregate Sum of values in a list of dictionaries for all documents

Question

I have a collection 'comments' as follows:

{
comment_id:10001
aspects:[
 {
   name:'aspectA',
   positive:2
   negative:3
   neutral:1
  },
 {
   name:'aspectB',
   positive:1
   negative:5
   neutral:3
  }
},
{
comment_id:10002
aspects:
 {
   name:'aspectA',
   positive:2
   negative:1
   neutral:2
  },
 {
   name:'aspectB',
   positive:3
   negative:4
   neutral:1
  }
}
]

the count of documents in comments is greater than 100K. I have to find the count of positive, negative and neutral for all aspects, ie. the sum of positive, negative and neutral for each aspect from the aspects field (which is a list of dicts as mentioned above) for all documents. I found that mapreduce can be used to do the task but I couldn't find enough docs to build a query.

Is there a way to find this using one query?

Add your Expected Output, it would help us in understanding what you are expecting and also post what you have tried so far? — Clement Amarnath, Apr 17 '17 at 11:39
Possible duplicate of [Sum in nested document MongoDB](http://stackoverflow.com/questions/14570577/sum-in-nested-document-mongodb) — s7vr, Apr 17 '17 at 11:46

score 2 · Accepted Answer · answered Apr 17 '17 at 18:10

2

To sum by aspects.name you can use the following aggregation :

db.comments.aggregate([{
    $unwind: "$aspects"
}, {
    $group: {
        _id: "$aspects.name",
        "positive": { $sum: "$aspects.positive" },
        "negative": { $sum: "$aspects.negative" },
        "neutral": { $sum: "$aspects.neutral" }
    }
}])

With pymongo :

from pymongo import MongoClient
import pprint

client = MongoClient('localhost', 27017)

db = client.testDB

pipeline = [
    {"$unwind": "$aspects"},
    {"$group": {
        "_id": "$aspects.name", 
        "positive": { "$sum": "$aspects.positive" }, 
        "negative": { "$sum": "$aspects.negative" }, 
        "neutral": { "$sum": "$aspects.neutral" }
        }
    }
]

pprint.pprint(list(db.comments.aggregate(pipeline)))

answered Apr 17 '17 at 18:10

Bertrand Martel

42,756
16
135
159

This works fine. So using aggregate is better than using MapReduce? – Hanzal Salim Apr 18 '17 at 08:57
Yes aggregation is more efficient. See [this](https://sysdig.com/blog/mongodb-showdown-aggregate-vs-map-reduce/). Consider accepting the answer if it solves your problem – Bertrand Martel Apr 18 '17 at 09:07
If I had a `date_posted` field in the collection is there any way i can get the counts of each aspect for each date from a certain date? – Hanzal Salim Apr 18 '17 at 09:11
Is it an ISODate ? Or a string like YYYY/MM/DD ? You could add this field to the group id : `_id : { name : "$aspects.name", date : "$date_posted" }` – Bertrand Martel Apr 18 '17 at 09:16
It's an ISO date. – Hanzal Salim Apr 18 '17 at 09:20

Mongo Aggregate Sum of values in a list of dictionaries for all documents

1 Answers1