0

I'm trying to aggregate using group on datetime value on below collection,

{
    "_id" : 104801,
    "requests" : {
        "rstTimeStamp" : ISODate("2014-11-11T10:30:13.577Z"),
        "emailId" : "user1@example.com"
    }
}
{
    "_id" : 104802,
    "requests" : {
        "rstTimeStamp" : ISODate("2014-11-11T12:40:13.577Z"),
        "emailId" : "user2@example.com"
    }
}
{
    "_id" : 104803,
    "requests" : {
        "rstTimeStamp" : ISODate("2014-11-12T12:40:13.577Z"),
        "emailId" : "user3@example.com"
    }
}

And expecting the output to show total number of requests for particular date for e.g. date 2014-11-11 has 2 requests. But due to the time value in the date it is considering every date as a single document.

Any help on this will be appreciated.

EDIT: My query is as follows:

db.clrequests.aggregate({ $group : { _id : "$requests.rstTimeStamp", total : {$sum : 1} } })
ZeMoon
  • 20,054
  • 5
  • 57
  • 98
Muthu Nadar
  • 47
  • 8
  • 1
    Could you post the aggregation query you have created? – ZeMoon Aug 17 '15 at 08:55
  • Here is my query, db.clrequests.aggregate({ $group : { _id : "$requests.rstTimeStamp", total : {$sum : 1} } }) – Muthu Nadar Aug 17 '15 at 09:20
  • In above duplicate link follow that aggregate code and add `$project` after group like this `{"$project":{"_id":{"$add": [new Date(0), "$_id"]},"count":1}}` it will return you expected output – Neo-coder Aug 17 '15 at 09:48
  • Thank you @Yogesh I solved using query "db.clrequests.aggregate([ { $match:{"requests":{$exists:true}} }, { $group: { _id:{ $substr: [ "$requests.rstTimeStamp", 0, 10 ] }, total : {$sum : 1} } }, { $sort : { _id: 1 } } ])" – Muthu Nadar Aug 17 '15 at 11:24

1 Answers1

0

You need to use the Date Aggregaetion operator for this :

Please check the below query :

db.exp8.aggregate([ 
                    { $group : 
                      { _id: 
                        { year : {$year:"$requests.rstTimeStamp"},
                          month: {$month:"$requests.rstTimeStamp"},
                          day :  {$dayOfMonth:"$requests.rstTimeStamp"}
                        },
                        Total : { $sum : 1 } , 
                        dte : {$first:"$requests.rstTimeStamp"} 
                       }
                     },
                     { $project : { _id : "$dte" , Total : 1 } } 
                   ]);

OutPut :

{ "_id" : ISODate("2014-11-12T12:40:13.577Z"), "Total" : 1 }
{ "_id" : ISODate("2014-11-11T10:30:13.577Z"), "Total" : 2 }
Yathish Manjunath
  • 1,919
  • 1
  • 13
  • 23
  • I got a error saying "uncaught exception: aggregate failed: { "errmsg" : "exception: can't convert from BSON type EOO to Date", "code" : 16006, "ok" : 0 }" – Muthu Nadar Aug 17 '15 at 09:30
  • You mite have one or more docs with a "requests.rstTimeStamp" value that's not a BSON Date. There is a sloution in the below Stack overflow link for that error, Please check : http://stackoverflow.com/questions/28415995/exception-cant-convert-from-bson-type-eoo-to-date – Yathish Manjunath Aug 17 '15 at 09:31
  • I'm expecting the output as { "_id" : ISODate("2014-11-11"), "total" : 2.0000000000000000 }, { "_id" : ISODate("2014-11-12"), "total" : 1.0000000000000000 } – Muthu Nadar Aug 17 '15 at 09:33
  • check now, i have modiofied the solution as per your needs. – Yathish Manjunath Aug 17 '15 at 09:55