按日期分组mongoDB [英] Group by Date mongoDB

问题描述

我在mongoDB中有一组数据，必须按$timestamp分组进行汇总.该字段包含日期，但格式为字符串(上面的示例数据).

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).

我应该如何继续将$timestamp转换为日期，以便将它们全部组合在一起?

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

接下来，我必须对每个日期和id的每个scores_today进行求和，并对每个scores_total进行相同的计算.

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

示例数据:

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

我的代码

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()....

这是node.js服务记录的错误

This is the error logged by node.js service

Err:{[MongoError:异常:无法从BSON类型的String转换为 日期]名称:"MongoError"，消息:例外:无法从 BSON类型字符串到日期"，errmsg:例外:无法从 BSON类型String to Date"，代码:16006，确定:0}

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 }

推荐答案

您可以使用ISODate($timestamp)从字符串构造Date对象.

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" }
            }
        };

更新

如果您没有运行MongoDb shell，则不能直接使用ISODate.在这种情况下，请尝试调用eval命令.

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;                                                                        '+        
'  }                                                                                       '+
'
);

这篇关于按日期分组mongoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！