MongoDB group by type and by date

Question

I have a Collection with a simple Document to store impressions and conversions with the following structure:

/* 1 */ { "_id" : ObjectId("566f1ef857c1e6dd3123050a"), "path_id" : ObjectId("55944c1ebe244fd19cbf510b"), "data_type" : "impression", "created_at" : ISODate("2015-12-14T19:56:40.100Z"), "__v" : 0 } /* 2 */ { "_id" : ObjectId("566f1fc9ac964e6f327c55d6"), "path_id" : ObjectId("55944c1ebe244fd19cbf510b"), "data_type" : "conversion", "created_at" : ISODate("2015-12-14T20:00:09.972Z"), "__v" : 0 } /* 3 */ { "_id" : ObjectId("566f2896739f6afa4485f327"), "path_id" : ObjectId("562e594315ef3d8c3f05d219"), "data_type" : "impression", "created_at" : ISODate("2015-12-14T20:37:42.139Z"), "__v" : 0 } /* 4 */ { "_id" : ObjectId("566f28e5739f6afa4485f328"), "path_id" : ObjectId("562e594315ef3d8c3f05d219"), "data_type" : "impression", "created_at" : ISODate("2015-12-14T20:39:01.233Z"), "__v" : 0 }

I'm able to group and count by data_type, but what I need to do is group by date and then count the data_type in order to get the following result:

[ { '_id': 'Y', 'conversions': 20, 'impressions': 2703, 'date': '2015-12-14' }, { '_id': 'Z', 'conversions': 10, 'impressions': 1703, 'date': '2015-12-13' } ]

The code I have right now is the following, but it only groups by data_type. I'm trying to add a project to regroup by date with no luck so far.

var path_id = new mongoose.Types.ObjectId( req.body.path_id ); var match = { 'path_id': { $eq: path_id } }; var group = { '_id': '$data_type', 'count': { '$sum': 1 } } Hit.aggregate( [ { $match: match }, { $group: group } ], function( err, res ) { console.log( res ); } );

The result is

POST /api/hits/bypath 200 30ms - 15b [ { _id: 'conversion', count: 2 }, { _id: 'impression', count: 2703 } ]

What do you mean by I'm trying to add a project to regroup by date? what is the expected result? — Sede
– Sede, Commented Dec 16, 2015 at 22:21

Suresh Mahawar · Accepted Answer · 2015-12-17 13:36:03Z

To do nested group by date, you have to use to Date aggregation operator $dateToString.

Here is query

db.hits.aggregate([ { "$project": { "created_at": { "$dateToString": { "format": "%Y-%m-%d", "date": "$created_at" } }, "data_type": true } }, { "$group": { "_id": { "data_type": "$data_type", "created_at": "$created_at" }, "count": { "$sum": 1 } } }, { "$group": { "_id": { "data_type": "$_id.data_type" }, "data":{ "$addToSet" : { count: "$count", date: "$_id.created_at" } } } } ])

If you want to match before group by operation based on condition, Add as following in the query

{ "$match": { "path_id": { "$eq": "<path_id>" } } }

marios vladimirou · Accepted Answer · 2015-12-16 23:30:34Z

you can use Date Aggregation Operators to project the day/month/year fields and then group by them

{ "$project": { "y": { "$year": "$created_at" }, "m": { "$month": "$created_at" }, "d": { "$dayOfMonth": "$created_at" }, "data_type" : 1 } }, { "$group": { "_id": { "year": "$y", "month": "$m", "day": "$d", "data_type": "$data_type" }, count: { "$sum": 1 } } }

and will output in this format:

 "_id": { "year": 2015, "month": 10, "day": 5, "data_type": "impression" }, count: 10

and then group again by date to combine the types in one document

{ "$group": { "_id": { "year": "$_id.year", "month": "$_id.month", "day": "$_id.day" }, types: {"$push":"$_id.data_type"}, counters: {"$push":"$count"} } }

which will result in this:

 "_id": { "year": 2015, "month": 10, "day": 5 }, types: ["impression", "conversion"] counters: [10, 5]

there might be a more elegant or faster (with 1 group) way to do this though, i am not sure.

Collectives™ on Stack Overflow

MongoDB group by type and by date

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related