MongoDB order by a sum on a subset

mongodb, sorting, sum

Solution

You were very close with your attempt. The only thing missing is the `$unwind` aggregation operator. `$unwind` basically splits each document out based on a sub-document. So before you group the failures and alerts, you unwind the errors, like so:

db.error_reports.aggregate(
  { $unwind : '$errors' },
  { $group : {
    _id : '$_id',
    'failures' : { $sum : '$errors.failures' },
    'alerts' : { $sum : '$errors.alerts' }
  } },
  { $sort : { 'failures': -1 } }
);

Which gives you the follow result:

{
    "result" : [
        {
            "_id" : ObjectId("5184de1262"),
            "failures" : 12,
            "alerts" : 3
        },
        {
            "_id" : ObjectId("5184de1261"),
            "failures" : 9,
            "alerts" : 5
        }
    ],
    "ok" : 1
}

Problem

I have the following collection: ``` error_reports [ { "_id":{ "$oid":"5184de1261" }, "date":"29/04/2013", "errors":[ { "_id":"10", "failures":2, "alerts":1, }, { "_id":"11", "failures":7, "alerts":4, } ] }, { "_id":{ "$oid":"5184de1262" }, "date":"30/04/2013", "errors":[ { "_id":"15", "failures":3, "alerts":2, }, { "_id":"16", "failures":9, "alerts":1, } ] } ] ``` Is it possible to retrieve the list of documents with failures and alerts sum sorted by failures in descending order? I am new to mongodb, I have been searching for 2 days but I can't figure out what is the proper query... I tried something like this : ``` db.error_reports.aggregate( { $sort : { failures: -1} }, { $group: { _id: "$_id", failures: { "$sum": "$errors.failures" } } } ); ``` But it didn't work, I think it is because of the `$sum`: `$errors.failures` thing, I would like to sum this attribute on every item of the `day_hours` subcollection but I don't know of to do this in a query...

Original source