CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Baminge
Baminge

Reputation: 31

MongoDB Aggregation group and count strings

I have a problem with counting different LogStatusses from my collection. I'd like the following result from a query:

Month | ImporterName | NrOfError | NrOfDebug | NrOfInfo | NrOfWarning

So this includes grouping by Month and ImporterName and counting the number of documents with the different statusses.

My MongoDB Collection: 

{
    "_id" : "8ec84cb7-5099-4a9d-be00-a40200a67c5a",
    "Messages" : [
        {
            "LogStatus" : "Error",
            "Message" : "My test message"
        },
        {
            "LogStatus" : "Error",
            "Message" : "My test message"
        },
        {
            "LogStatus" : "Error",
            "Message" : "My test message"
        },
        {
            "LogStatus" : "Error",
            "Message" : "My test message"
        },
        {
            "LogStatus" : "Error",
            "Message" : "My test message"
        }
    ],
    "StartTime" : new Date("2014-12-15T10:06:09.00Z"),
    "EndTime" : new Date("2014-12-15T13:06:09.00Z"),
    "HasErrors" : true,
    "HasWarnings" : false,
    "ImporterName" : "MyImporter"
}

I already have the following query's:

db.SessionLogItems.aggregate
([
{
    $project:
    {
        month :{$month : "$StartTime"},
        name: "$ImporterName",
        status: "$Messages.LogStatus", 
        _id: 0
    }
}
])

result:

month: 12, "name" : "importername", status: ["Error", "Error", "Info"]

and 

db.SessionLogItems.aggregate
([
{ 
    $unwind: "$Messages" 
}, 
{
    $group: { _id: "$Messages", Number : {$sum : 1 }}
}, 
{
    $sort: {Number : -1 }
} 
])

result: "_id" : { "LogStatus" : "Warning", "Message" : "My test warning" }, "Number" :5 "_id" : { "LogStatus" : "Error", "Message" : "My test message" }, "Number" : 5

But I can't seem to figure out the correct query. Any help is appreciated!

EDIT:

My example above is just one out of many documents. I have several importers which have a startTime and EndTime. The importers have several logmessages and four possible LogStatusses: "Error", "Info", "Debug", "Warning". I'd like to have an overview per month and per importer how many errors, infos, debugs and warnings they produced.

Upvotes: 1

Views: 2096

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151072

Assuming there is no overlap in your "month" between StartTime and EndTime values then you can simply use the StartTime value as the basis for a grouping key. Most of the magic for your other "fields" comes from the $cond operator which decides whether to count the value or not:

db.SessionLogItems.aggregate([

    // Unwind the array to de-normalize the documents contained
    { "$unwind": "$Messages" },

    // Month and Importer form the grouping key
    { "$group": {
        "_id": { 
            "month": { "$month": "$StartTime" },
            "ImporterName": "$ImporterName"
        },
        "NrOfError": {
           "$sum": {
               "$cond": [
                   { "$eq": [ "$Messages.LogStatus", "Error" ] },
                   1,
                   0
                ]
            }
        },
        "NrOfDebug": {
           "$sum": {
               "$cond": [
                   { "$eq": [ "$Messages.LogStatus", "Debug" ] },
                   1,
                   0
                ]
            }
        },
        "NrOfInfo": {
           "$sum": {
               "$cond": [
                   { "$eq": [ "$Messages.LogStatus", "Info" ] },
                   1,
                   0
                ]
            }
        },
        "NrOfWarning": {
           "$sum": {
               "$cond": [
                   { "$eq": [ "$Messages.LogStatus", "Warning" ] },
                   1,
                   0
                ]
            }
        }
    }}
])

So basically the "Status" value is tested and where it is matched or not then the appropriate count value is added to the appropriate field.

Upvotes: 1

Related Questions