Unknown
Unknown

Reputation: 5772

Mongodb aggregate (count) on multiple fields simultaneously

I've got documents that look like this:

{
    "_id" : "someuniqueeventid",
    "event" : "event_type_1",
    "date" : ISODate("2014-01-14T00:00:00Z"),
}

I want to group by "event" and count how many of each event type occured in each day of the week. Basically, I want to get something like:

{
    "_id": "event_type_1",
    "1": "number of event_type_1 for Monday",
    "2": "number of event_type_1 for Tuesday",
    ...
},
{
    "_id": "event_type_2",
    ...
}

Unfortunately, I'm stuck at:

db.data.aggregate([ {$project: {date_of_week: {$dayOfWeek: "$date"}, event: "$event"}}, 
                    {$group: {_id: "$event", .... } ])

Any ideas?

Upvotes: 23

Views: 34084

Answers (2)

Neil Lunn
Neil Lunn

Reputation: 151092

The aggregation framework won't create keys based on data, nor should you even be doing so as "data" is not a key but actually data, so you should stick to the pattern.

That means you can basically just do this:

db.data.aggregate([
    { "$group": {
        "_id": {
            "event_type": "$event",
            "day": { "$dayOfWeek": "$date" }
        },
        "count": { "$sum": 1 } 
    }}
])

And that will count the occurrences per day of week per event, albeit in multiple documents in the output, but this is easy to change to a single document per event:

db.data.aggregate([
    { "$group": {
        "_id": {
            "event_type": "$event",
            "day": { "$dayOfWeek": "$date" }
        },
        "count": { "$sum": 1 } 
    }},
    { "$group": {
        "_id": "$_id.event_type",
        "days": { "$push": { "day": "$_id.day", "count": "$count" } }
    }}
])

And that is in an array form, but it still holds the results you want.

If you are really bent on doing your exact form then you want to do something like this:

db.data.aggregate([
    { "$group": {
        "_id": "$event",
        "1": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 1 ] },
                    1,
                    0
                ]
            }
        },
        "2": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 2 ] },
                    1,
                    0
                ]
            }
        },
        "3": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 3 ] },
                    1,
                    0
                ]
            }
        },
        "4": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 4 ] },
                    1,
                    0
                ]
            }
        },
        "5": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 5 ] },
                    1,
                    0
                ]
            }
        },
        "6": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 6 ] },
                    1,
                    0
                ]
            }
        },
        "7": {
            "$sum": {
                "$cond": [
                    { "$eq": [{ "$dayOfWeek": "$date" }, 7 ] },
                    1,
                    0
                ]
            }
        }
    }}
)

But that is really long winded so IMHO I would stick with the first or maybe second solution as they are shorter and more easy to read.

Upvotes: 27

chridam
chridam

Reputation: 103335

With MongoDb 3.4.4 and newer, you can leverage the use of $arrayToObject operator to get the counts. You would need to run the following aggregate pipeline:

db.data.aggregate([
    { 
        "$group": {
            "_id": {
                "event": "$event",
                "day": { "$substr": [ { "$dayOfWeek": "$date" }, 0, -1 ] }
            },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.event",
            "counts": {
                "$push": {
                    "k": "$_id.day",
                    "v": "$count"
                }
            }
        }
    },
    { 
        "$project": {
            "counts": { "$arrayToObject": "$counts" }
        } 
    }    
])

Upvotes: 6

Related Questions