CODEWITHSUNDEEP
mongodb
JamesE
JamesE

Reputation: 3923

MongoDB aggregation counts based on value

I have a MongoDB query I am trying to figure out. I'd like to group my data by date and one other field (portfolio) and get the counts for each buildResult in that grouping.

Sample data looks like this:

    {
            "_id" : ObjectId("52dea764e4b0a491abb54102"),
            "type" : "build",
            "time" : ISODate("2014-01-21T16:59:16.642Z"),
            "data" : {
                    "buildNumber" : 35,
                    "buildDuration" : 1034300,
                    "portfolio" : "Shared",
                    "buildResult" : "FAILURE"
            }
    }
    {
            "_id" : ObjectId("52dea7b7e4b0a491abb54103"),
            "type" : "build",
            "time" : ISODate("2014-01-21T17:00:39.617Z"),
            "data" : {
                    "buildNumber" : 13,
                    "buildDuration" : 1186028,
                    "portfolio" : "Sample",
                    "buildResult" : "SUCCESS"
            }
    }

The output I am shooting for would be something like this:

    {
            "result" : [
                    {
                            "_id" : {
                                    "month" : 2,
                                    "day" : 28,
                                    "year" : 2014,
                                    "portfolio" : "Shared"
                            },
                            "aborted" : 3,
                            "failure" : 0,
                            "unstable" : 0,
                            "success" : 34
                    },
                    {
                            "_id" : {
                                    "month" : 2,
                                    "day" : 28,
                                    "year" : 2014,
                                    "portfolio" : "Sample"
                            },
                            "aborted" : 3,
                            "failure" : 2,
                            "unstable" : 0,
                            "success" : 37
                    }
            ],
            "ok" : 1
    }

My current query is:

db.builds.aggregate([    
    { $match: { "data.buildResult" : { $ne : null} }},
    { $group: {          
        _id: {              
            month: { $month: "$time" },             
            day: { $dayOfMonth: "$time" },             
            year: { $year: "$time" },   
            portfolio: "$data.portfolio",                    
        },         
        aborted: { $sum: { "$data.buildResult": "ABORTED" } },
        failure: { $sum: { "$data.buildResult": "FAILURE" } },
        unstable: { $sum: { "$data.buildResult": "UNSTABLE" } },
        success: { $sum: { "$data.buildResult": "SUCCESS" } }
    } },     
    { $sort: { "_id.day": 1, "_id.month": 1, "_id.year": 1 } } 
])

I have tried many variations with the following lines including $match, $in and other operators. Any help would be very appreciated.

aborted: { $sum: { "$data.buildResult": "ABORTED" } },
failure: { $sum: { "$data.buildResult": "FAILURE" } },
unstable: { $sum: { "$data.buildResult": "UNSTABLE" } },
success: { $sum: { "$data.buildResult": "SUCCESS" } }

Upvotes: 2

Views: 1062

Answers (1)

joao
joao

Reputation: 4117

To achieve that you can use the $cond and $eq operators like this:

aborted: {$sum: {$cond : [{$eq : ["$data.buildResult", "ABORTED"]}, 1, 0]}}

Edit: As noted by Neil Lunn in the comments, the $cond here is irrelevant because the $eq operator already returns 0 or 1.

aborted: {$sum: {$eq : ["$data.buildResult", "ABORTED"]}}

Upvotes: 3

Related Questions