Aggregation Grouping by Two Fields in MongoDB

Question

I have a schema like the following:

Event : {
    eventType : Number,
    created : Date,
}

My end goal is to create a line graph for each eventType that shows how many of each event was posted daily.

I've never tried the MongoDB aggregation functions, so I'm a little confused on how to go about doing this. I read through the MongoDB aggregation documentation and my initial thought is to do two grouping and one project passes:

Group every event into a day
Group the results of this by eventType
Project these results so the output is in a nice format to graph.

So my output would look something like this (so I can put it on line graphs:

{
  [
    { 
        eventType: 0, 
        days : [ ISODate(2015-01-01), ISODate(2015-01-02), ISODate(2015-01-03) ], 
        totals: [ 0, 15, 3 ] 
    }, {
        eventType: 1, 
        days : [ ISODate(2015-01-01), ISODate(2015-01-02), ISODate(2015-01-03) ], 
        totals: [ 4, 5, 2 ] 
    }, {        
    ...
  ]
}

I'm not sure if conceptually that's correct, and I'm even less sure about the syntax this would require. Any help would be appreciated!

Blakes Seven · Accepted Answer · 2015-08-14 03:03:02Z

Yes the basic idea here is to group twice, and you also want to make use of the date aggregation operators:

db.event.aggregate([
    { "$group": {
        "_id": {
            "eventType":"$eventType",
            "date": { 
                "year": { "$year": "$created" },
                "month": { "$month": "$created" },
                "day": { "$dayOfMonth": "$created" }
            }
        },
        "total": { "$sum": 1 }
    }},
    { "$group": {
        "_id": "$_id.eventType",
        "days": { "$push": "$_id.date" },
        "totals": { "$push": "$total" }
    }}
])

Or you can use date math to return a timestamp value instead:

db.event.aggregate([
    { "$group": {
        "_id": {
            "eventType":"$eventType",
            "date": { 
                "$subtract": [
                    { "$subtract": [ "$created", new Date(0) ] },
                    { "$mod": [
                        { "$subtract": [ "$created", new Date(0) ] },
                    ]}
                ]
            }
        },
        "total": { "$sum": 1 }
    }},
    { "$group": {
        "_id": "$_id.eventType",
        "days": { "$push": "$_id.date" },
        "totals": { "$push": "$total" }
    }}
])

Personally, two arrays does not seem as readable as one does to me. So I prefer this:

db.event.aggregate([
    { "$group": {
        "_id": {
            "eventType":"$eventType",
            "date": { 
                "$subtract": [
                    { "$subtract": [ "$created", new Date(0) ] },
                    { "$mod": [
                        { "$subtract": [ "$created", new Date(0) ] },
                    ]}
                ]
            }
        },
        "total": { "$sum": 1 }
    }},
    { "$group": {
        "_id": "$_id.eventType",
        "days": { 
            "$push": { 
                "date": "$_id.date", "total": "$total"
            }
        }
    }}
])

Where each array index already contains all of the information.

Most graphing packages are really quite flexible when you really look at the documentation about supported data structures. It is a common MongoDB outuput request to format data in a particular way ( all in arrays ) for a "popular package", but that package actually does support the standard object lists as long as you configure it too. It's just that all the "basic examples" don't tell you otherwise.

Collectives™ on Stack Overflow

Aggregation Grouping by Two Fields in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related