How to use Mongo aggregate feature to compute Totals and SubTotal Percentages with Nested Groups?

Question

I have some documents in mongo collection like this.

 {
    "_id" : ObjectId("57e290087139be15d59408c1"),
    "groupName" : "Registration",
    "testCases" : [ 
        {
            "name" : "R1",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "R1.1",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "R2",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "R3",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "R4",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "R1",
            "browser" : "Firefox",
            "status" : "passed"
        }, 
        {
            "name" : "R2",
            "browser" : "Firefox",
            "status" : "passed"
        }, 
        {
            "name" : "R3",
            "browser" : "Firefox",
            "status" : "passed"
        }, 
        {
            "name" : "R4",
            "browser" : "Firefox",
            "status" : "failed"
        }
    ]
}

    {
    "_id" : ObjectId("57e2903b7139be15d59408c2"),
    "groupName" : "Checkout",
    "testCases" : [ 
        {
            "name" : "C1",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "C2",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "C3",
            "browser" : "Chrome",
            "status" : "failed"
        }, 
        {
            "name" : "C4",
            "browser" : "Chrome",
            "status" : "passed"
        }, 
        {
            "name" : "C1",
            "browser" : "Firefox",
            "status" : "passed"
        }, 
        {
            "name" : "C2",
            "browser" : "Firefox",
            "status" : "passed"
        }, 
        {
            "name" : "C3",
            "browser" : "Firefox",
            "status" : "passed"
        }, 
        {
            "name" : "C4",
            "browser" : "Firefox",
            "status" : "failed"
        }
    ]
}

How do I use Mongo's aggregate feature to compute totals and subtotal percentages with nested groups?

I am expecting an output with results as

Example Expected Output:

results: [    
    {
        "groupName": "Registration",
        "totalTests": 17,
        "section": [
            { "name": "R1", "totalTests": 17, "sectionCount": 2 }
        ]
    }
]

score 2 · Accepted Answer · answered Sep 21 '16 at 15:27

You can try running the following aggregation operation:

db.collection.aggregate([
    { "$unwind": "$testCases" },
    {
        "$group": {
            "_id": null,
            "testsCount": { "$sum": 1 },
            "docs": { "$push": "$$ROOT" }
        }
    },
    { "$unwind": "$docs" },
    {
        "$group": {
            "_id": {
                "groupName": "$docs.groupName",
                "testName": "$docs.testCases.name"
            },
            "count": { "$sum": 1 },
            "testsCount": { "$first": "$testsCount" }
        }
    },    
    {
        "$group": {
            "_id": "$_id.groupName",
            "totalTests": { "$first": "$testsCount" },
            "section": {
                "$push": {
                    "name": "$_id.testName", 
                    "totalTests": "$testsCount", 
                    "sectionCount": "$count"
                }
            }
        }
    }
])

Thanks Chridam. I never knew that there are few keywords which can help in aggregate functions like $$ROOT,$push,$first. Thanks again for showing the light on this. Now my query has been reduced from 100+ lines of code to less than 40. — Nirmal, Sep 23 '16 at 03:19

How to use Mongo aggregate feature to compute Totals and SubTotal Percentages with Nested Groups?

1 Answers1