Aggregation involving sum of array field(s) and normal field(s) in mongodb

Question

This is the sample collection:

{'f1': 10, '_id': 1, 'key': 'g', 'items': [{'i1': 10}, {'i1': 10}, {'i1': 10}]}
{'f1': 10, '_id': 2, 'key': 'g', 'items': [{'i1': 10}, {'i1': 10}, {'i1': 10}]}
{'f1': 77, '_id': 3, 'key': 'g', 'items': [{'i1': 10}, {'i1': 10}, {'i1': 10}]}

I want a formula like: $sum(f1 + Σ[items.i1]) to be computed on the above collection. Following is what I could come up with (in pymongo):

db.collec.aggregate([    
        { "$unwind" : "$items"},
    { "$group" : {    
             "_id" : {"key": "$key", "id": "$_id"},    
     "matches" : { "$sum" : "$items.i1" },    
     "extra" : { "$sum" : "$f1" },    
     "count" : {"$sum": 1}    
                    }},
    { "$group": {    
             "_id" : "$_id.key",     
             "finalSum":{ "$sum":    
                        { "$add": ["$matches", {"$divide":["$extra", "$count"]}]}}}}      
    ]);

Output:

{'finalSum': 187.0, '_id': 'g'}

Although this gives correct output, I hope there's a better, simpler solution for this: Any help highly appreciated.

Linda Qin · Accepted Answer · 2013-04-12 05:54:56Z

3

When you are grouping the documents, you can save "f1" to the _id field, so that you don't need to summarize it and divide it for each document.

The aggregation operation is like this:

db.collec.aggregate([    
    { "$unwind" : "$items"},
    { "$group" : {    
          _id : {key: "$key", id: "$_id", f1 : "$f1" },    
          matches : { "$sum" : "$items.i1" },    
    }},
    { "$group": {    
          _id : "$_id.key",     
          finalSum : { "$sum":    
                        { "$add": ["$matches", "$_id.f1"]}}}}      
    ]);

answered Apr 12, 2013 at 5:54

Linda Qin

1,0569 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ranjjose Over a year ago

Thanks Linda. This seems to be a better solution. However I'm wondering about the 16mb document limit possibly disallowing this in-case of huge number of records and sufficient unique key - f1 combinations. Also, is there any standard way to this? Other than writing multiple $group ?

Collectives™ on Stack Overflow

Aggregation involving sum of array field(s) and normal field(s) in mongodb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related