CODEWITHSUNDEEP
mongodb
vmr
vmr

Reputation: 1935

MongoDB query on list of nested documents

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

Upvotes: 3

Views: 277

Answers (1)

Parvin Gasimzade
Parvin Gasimzade

Reputation: 26012

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

Upvotes: 1

Related Questions