MongoDB query on list of nested documents

Question

I am not a newbie to MongoDB but new to aggregation concepts... I have collection data which looks something like this, currently it contains 2 documents

 {
    "_id" : ObjectId("52cc0b079f0ae55e9fb770f8"),
    "uid" : 100,
    "data" : {
        "mi" : [ 
            {
                "miId" : NumberLong(1),
                "name" : "ABC",
                "severity" : "HIGH",
                "failures" : NumberLong(2),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }, 
            {
                "miId" : NumberLong(10),
                "name" : "PQR",
                "severity" : "HIGH",
                "failures" : NumberLong(3),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }
}

{
    "_id" : ObjectId("52cc0b079f0ae55easdas8"),
    "uid" : 200,
    "data" : {
        "mi" : [ 
            {
                "miId" : NumberLong(10),
                "name" : "ABC",
                "severity" : "HIGH",
                "failures" : NumberLong(20),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }, 
            {
                "miId" : NumberLong(18),
                "name" : "PQR",
                "severity" : "HIGH",
                "failures" : NumberLong(30),
                "description" : "Some description",
                "remediation" : "Some remedy"
            }
      }
}

How do I come up with a query in MongoDB shell or Java which does groupby() based on "name" and sums up all "failures", the result should also contain "uid" of the "name" with the highest "failures". The result should look something like this:

{
{
    "_id" : ObjectId("508894efd4197aa2b9490741"),
    "name" : "ABC",
    "sum_total_of_failures" : 22,
    "uid" : 200
}

{
    "_id" : ObjectId("508894efd4197aa2b9490741"),
    "name" : "PQR",
    "sum_total_of_failures" : 33,
    "uid" : 200
}
}

Any help will be really appreciated, I wrote a query with $unwind as "mi" documents are stored in a list but it returned empty result. The query is as below:

    db.temp.aggregate(
{$unwind: "$mi"}, 
{$project: {mi : "$mi"}},
{$group: { _id: "$name",total: { $sum: "$failures" }}})

Parvin Gasimzade · Accepted Answer · 2014-01-10T09:24:14.307

Try the following query :

db.collection.aggregate(
{$unwind : "$data.mi"},
{$sort : {"data.mi.failures" : -1}},
{$group : {_id : "$data.mi.name", 
           sum_total_of_failures : {$sum : "$data.mi.failures"}, 
           uid : {$first : "$uid"}}}
)

The result will be like :

"result" : [
    {
        "_id" : "PQR",
        "sum_total_of_failures" : NumberLong(33),
        "uid" : 200
    },
    {
        "_id" : "ABC",
        "sum_total_of_failures" : NumberLong(22),
        "uid" : 200
    }
]

With Java driver you can do it as follows :

    DBCollection coll = ...

    DBObject unwind = new BasicDBObject("$unwind", "$data.mi");
    DBObject sort = new BasicDBObject("$sort", new BasicDBObject("data.mi.failures", -1));

    DBObject groupObj = new BasicDBObject();
    groupObj.put("_id", "$data.mi.name");
    groupObj.put("sum_total_of_failures", new BasicDBObject("$sum", "$data.mi.failures"));
    groupObj.put("uid", new BasicDBObject("$first", "$uid"));

    DBObject group = new BasicDBObject("$group", groupObj);

    AggregationOutput output = coll.aggregate(unwind, sort, group);
    if (output != null) {
        for (DBObject result : output.results()) {
            String name = (String) result.get("_id");
            Long sumTotalOfFailures = (Long) result.get("sum_total_of_failures");
            Integer uid = (Integer) result.get("uid");
        }
    }

Thanks!!! , Small problem though, The "sum_total_of_failures" is now coming correctly but, the "uid" is always 200 , the "uid" should be of the document with highest "sum_total_of_failures" for that name. — vmr, Jan 08 '14 at 15:05
I thought you want the highest uid. I have updated query. Before grouping I sorted data by failures and get the first matched uid. Hope this will solve your problem. — Parvin Gasimzade, Jan 08 '14 at 15:26

MongoDB query on list of nested documents

1 Answers1

Linked