Group by Date mongoDB

Question

I have a set of data in mongoDB that I have to sum up grouped by $timestamp. This field contains a date, but's formatted as String (example data above).

How should I proceed to convert $timestamp into a date so I can group them all together?

Next, I have to sum each scores_today for each date and iden, and the same with each scores_total.

Example data:

[
    {
        _id: "1442",
        timestamp: "2016-03-15T22:24:02.000Z",
        iden: "15",
        scores_today: "0.000000",
        scores_total: "52337.000000"
    }
]

My code

var project = {
            "$project":{ 
                "_id": 0,
                "y": {
                    "$year": "$timestamp"      // tried this way, not working
                    },
                "m": {
                    "$month": new Date("$timestamp") // tried either this, not working
                }, 
                "d": {
                    "$dayOfMonth":  new Date("$timestamp")
                },
                "iden" : "$iden"
            } 
        },
        group = {   
            "$group": { 
                "_id": { 
                    "iden" : "$iden",
                    "year": "$y", 
                    "month": "$m", 
                    "day": "$d"
                },  
                "count" : { "$sum" : "$scores_today" }
            }
        };
        mongoDB.collection('raw').aggregate([ project, group ]).toArray()....

This is the error logged by node.js service

Err: { [MongoError: exception: can't convert from BSON type String to Date] name: 'MongoError', message: 'exception: can\'t convert from BSON type String to Date', errmsg: 'exception: can\'t convert from BSON type String to Date', code: 16006, ok: 0 }

Answer 1

You can construct Date object from string using ISODate($timestamp).

var project = {
            "$project":{ 
                "_id": 0,
                "y": {
                    "$year": ISODate("$timestamp").getFullYear()
                    },
                "m": {
                    "$month": ISODate("$timestamp").getMonth()+1 // months start from 0
                }, 
                "d": {
                    "$dayOfMonth":  ISODate("$timestamp").getDate()
                },
                "iden" : "$iden"
            } 
        },
        group = {   
            "$group": { 
                "_id": { 
                    "iden" : "$iden",
                    "year": "$y", 
                    "month": "$m", 
                    "day": "$d"
                },  
                "count" : { "$sum" : "$scores_today" }
            }
        };

UPDATE

If you're not running MongoDb shell then you can't use ISODate directly. In this case try to invoke eval command.

var aggregationResult=mongoDB.eval(
'
'function()                                                                                '+
'{                                                                                         '+
'  var project = {                                                                         '+
'              "$project":{                                                                '+ 
'                  "_id": 0,                                                               '+
'                  "y": {                                                                  '+
'                      "$year": ISODate("$timestamp").getFullYear()                        '+
'                      },                                                                  '+
'                  "m": {                                                                  '+
'                      "$month": ISODate("$timestamp").getMonth()+1 // months start from 0 '+
'                  },                                                                      '+
'                  "d": {                                                                  '+
'                      "$dayOfMonth":  ISODate("$timestamp").getDate()                     '+
'                  },                                                                      '+
'                  "iden" : "$iden"                                                        '+
'              }                                                                           '+
'          },                                                                              '+
'          group = {                                                                       '+
'              "$group": {                                                                 '+
'                  "_id": {                                                                '+
'                      "iden" : "$iden",                                                   '+
'                      "year": "$y",                                                       '+
'                      "month": "$m",                                                      '+
'                      "day": "$d"                                                         '+
'                  },                                                                      '+
'                  "count" : { "$sum" : "$scores_today" }                                  '+
'              }                                                                           '+
'          };  
'    var result=db.raw.aggregate([ project, group ]);                  '+
'    return result;                                                                        '+        
'  }                                                                                       '+
'
);