CODEWITHSUNDEEP
mongodb
JamesE
JamesE

Reputation: 3923

MongoDB aggregate return count of 0 if no results

I have the following MongoDB query that groups by date and result and gives a count. I'd like to have the query also return a count of 0 for a particular date and result if data doesn't exist for that day.

For example I have the following result statuses: SUCCESS and FAILED. If on the 21st there were no results of FAILED I would want a count returned of 0:

{
    "_id" : {
            "month" : 1,
            "day" : 21,
            "year" : 2014,
            "buildResult" : "FAILURE"
    },
    "count" : 0
}

I've done something similar with a relational database and a calendar table, but I'm not sure how to approach this with MongoDB. Is this possible or should I do something programatically after running the query?

Here is an example of a document (simplified) in the database:

    {
            "_id" : ObjectId("52deab2fe4b0a491abb54108"),
            "type" : "build",
            "time" : ISODate("2014-01-21T17:15:27.471Z"),
            "data" : {
                    "buildNumber" : 43,
                    "buildDuration" : 997308,
                    "buildResult" : "SUCCESS"
            }
    }

Here is my current query:

db.builds.aggregate([
    { $match: { "data.buildResult" : { $ne : null} }},
    { $group: { 
        _id: { 
            month: { $month: "$time" },
            day: { $dayOfMonth: "$time" },
            year: { $year: "$time" }, 
            buildResult: "$data.buildResult",
        },
        count: { $sum: 1}
    } },

    { $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1} }
])

Upvotes: 9

Views: 8264

Answers (1)

Ivan.Srb
Ivan.Srb

Reputation: 1851

If I correctly understand what do you want, you could try this: 

db.builds.aggregate([
    { $project: 
        { 
            time: 1,
            projectedData: { $ifNull: ['$data.buildResult', 'none'] } 
        } 
    },

    { $group: { 
        _id: { 
            month: { $month: "$time" },
            day: { $dayOfMonth: "$time" },
            year: { $year: "$time" }, 
            buildResult: "$projectedData"
        },
        count: { $sum: { $cond: [ { $eq: [ "$projectedData", "none" ] }, 0, 1 ] } }
    } },

    { $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1 } }
])

Update:
You want to get from output more documents that been in input, it is possible only with unwind operator that works with arrays, but you haven't any arrays, so as I know it is impossible to get more documents in your case. So you should add some logic after query result to create new data for existing dates with 0 count for another type of buildResult...

Upvotes: 1

Related Questions