MongoDB aggregate count

Question

I have the following documents in my collection

[
  {job : "(smoke) test-online", build : 1, status: 1, test: "test-search"},
  {job : "(smoke) test-online", build : 1, status: 1, test: "test-create"},
  {job : "(smoke) test-online", build : 2, status: 1, test: "test-search"},
  {job : "(smoke) test-online", build : 3, status: 1, test: "test-create"},
  {job : "(smoke) test-inside", build : 3, status: 1, test: "test-create"}
]

I want to get statistics for all the latest build projects: the number of tests with the status 1, 2, 3, 4.

But I could only get a number of tests in the latest build assemblies.

db.getCollection('test_results').aggregate(
  [
    {$match: {job: {$regex: 'test-online'}}},
    {$group: {_id: {job: "$job", build: "$build"}, lastBuild: {"$last": "$build"}, count: {$sum: 1}}}
  ]
)

How to find a certain status?

{
    "result" : [ 
        {
            "_id" : {
                "job" : "(smoke) test-online",
                "build" : 10
            },
            "lastBuild" : 10,
            "count" : 48.0000000000000000,
            "count_success": 12, //status 1
            "count_fail": 12, //status 2
            "count_skip": 12, //status 4
            "count_error": 12 //status 3
        }, 
        {
            "_id" : {
                "job" : "(smoke) test-inside",
                "build" : 272
            },
            "lastBuild" : 272,
            "count" : 60.0000000000000000,
            "count_success": 260,
            "count_fail": 6,
            "count_skip": 6,
            "count_error": 0,
        }
]

Answer 1

Try the following aggregation pipeline:

db.test_results.aggregate([
    {
        "$match": {
            "job": {"$regex": 'test-online'},
            "status": {"$in": [1,2,3,4]}
        }
    },
    {
        "$group": {
            "_id": {
                "job": "$job", 
                "build": "$build"
            }, 
            "lastBuild": {
                "$last": "$build"
            }, 
            "count": {"$sum": 1},
            "count_success": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$status", 1] },
                        1,
                        0
                    ]                    
                }
            },
            "count_fail": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$status", 2] },
                        1,
                        0
                    ]                    
                }
            },
            "count_skip": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$status", 4] },
                        1,
                        0
                    ]                    
                }
            },
            "count_error": {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$status", 3] },
                        1,
                        0
                    ]                    
                }
            }
        }
    } 
])

Output (with the above example):

/* 0 */
{
    "result" : [ 
        {
            "_id" : {
                "job" : "(smoke) test-online",
                "build" : 3
            },
            "lastBuild" : 3,
            "count" : 1,
            "count_success" : 1,
            "count_fail" : 0,
            "count_skip" : 0,
            "count_error" : 0
        }, 
        {
            "_id" : {
                "job" : "(smoke) test-online",
                "build" : 2
            },
            "lastBuild" : 2,
            "count" : 1,
            "count_success" : 1,
            "count_fail" : 0,
            "count_skip" : 0,
            "count_error" : 0
        }, 
        {
            "_id" : {
                "job" : "(smoke) test-online",
                "build" : 1
            },
            "lastBuild" : 1,
            "count" : 2,
            "count_success" : 2,
            "count_fail" : 0,
            "count_skip" : 0,
            "count_error" : 0
        }
    ],
    "ok" : 1
}