How to construct Date with aggregation framework in MongoDB

Question

I have simple MongoDB collection:

> db.installer.find().pretty()
{
"_id" : ObjectId("51a875c83146de1db23d25d4"),
"dt" : ISODate("2013-05-31T10:04:56.567Z"),
"ev" : "screenDisplay_welcome"
}
{
"_id" : ObjectId("51a875c83146de1db23d25d5"),
"dt" : ISODate("2013-05-31T10:04:56.568Z"),
"ev" : "distribution"
}
...
{
"_id" : ObjectId("51a87a4d3146de1e6a8de9e2"),
"dt" : ISODate("2013-05-31T10:24:13.901Z"),
"ev" : "screenDisplay_welcome"
}

'ev' - some event. 'dt' - datetime of this event

I want to get statistics about this events grouped by event name and event day:

> db.installer.aggregate([{$group:{_id:{ev:'$ev', dt:{$dayOfYear:'$dt'}}, sum:{$sum:1}}}, {$project:{sum:1, _id:0, name:'$_id.ev', dt:'$_id.dt'}}])
{
"result" : [
    {
        "sum" : 8,
        "name" : "uninstall",
        "dt" : 151
    },
    {
        "sum" : 3,
        "name" : "screenDisplay_installServices",
        "dt" : 151
    },
    {
        "sum" : 558,
        "name" : "distribution",
        "dt" : 151
    },
    {
        "sum" : 115,
        "name" : "screenDisplay_welcome",
        "dt" : 151
    }
],
"ok" : 1
}

Here i user $dayOfYear command to drop time for all events. Is there any way I can construct my Date object back inside aggregation pipeline?

e.g. "dt" : 151 will be "dt" : ISODate("2013-05-31T00:00:00.000Z")

No, not with the aggregation framework, you cannot construct a Date object in an aggregate object expression (at least in 2.2) :-/ Would the date as a string instead of a date be an option, or possibly using MapReduce? — Joachim Isaksson
– Joachim Isaksson, Commented May 31, 2013 at 13:13
don't use $dayOfYear (it doesn't work right when you have more than one year's worth of data anyway) instead you can use a trick I describe here: kamsky.org/1/post/2013/03/… - basically just drop the hours, minutes, seconds and milliseconds from the date before the $group phase. — Asya Kamsky
– Asya Kamsky, Commented Jun 1, 2013 at 7:43

jimoleary · Accepted Answer · 2013-05-31 13:50:17Z

2

While it's not currently possible to construct a date in the aggregation pipeline, you could just take the first datetime instead (and process it later in your client).

For Example :

db.installer.aggregate([     
  {$group:      
   {          
     _id:{ev:'$ev', doy:{$dayOfYear:'$dt'} },
     sum:{$sum:1},
     date:{$first: '$dt'}
   },
  },
  {$project:{sum:1,  _id:0, name:'$_id.ev', dt:'$_id.doy', date:1}}
])

answered May 31, 2013 at 13:50

jimoleary

1,65513 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2013-05-31 15:18:22Z

1

Just as an alternative to @jimoleary's solution, if you're processing it on the client anyway, why obfuscate the date to begin with? :)

db.installer.aggregate([
{ 
  $group: 
  { 
    _id: { ev: '$ev', doy: { $substr: ['$dt', 0, 10] } }, 
    sum: { $sum: 1 } 
  }
}, 
{
  $project:
  {
    sum: 1, _id: 0, name: '$_id.ev', dt: '$_id.doy'
  }
} 
])

answered May 31, 2013 at 15:18

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

1 Comment

jimoleary Over a year ago

nice! I prefer your solution.

Collectives™ on Stack Overflow

How to construct Date with aggregation framework in MongoDB

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related