mongoDb中的$dayOfMonth聚合

Question

我在mongodb中有一个集合，结构如下：

    {
"_id": {
    "$oid": "565272dbe4b00dbeb0fe76b9"
},
"ObjectTemp": 20.706992938189444,
"AmbientTemp": 26.78125,
"timeStamp": "2015-11-23T01:58:52.084Z"
      }

我想要应用一个聚合函数来获取1天内"ObjectTemp“值大于10且小于10的文档计数。我使用下面的查询来获得结果。“

     db.temp.aggregate([{
     $project: {
      day :{$dayOfMonth: { $substr: [ "$timeStamp", 0, 10] }},
    lessThan10: { $cond: [ { $lt: ["$ObjectTemp", 10 ] }, 1, 0]},
     moreThan10: {$cond:[ {  $gt: [ "$ObjectTemp", 10 ] }, 1, 0]}}
               },

       {
      $group: { 
     _id : "$day",
      countSmaller: { $sum: "$lessThan10" },
     countBigger: { $sum: "$moreThan10" }
      }
      },{$sort: { _id : -1 }}])

在执行时，我得到了错误：

             assert: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
    } : aggregate failed
      Error: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
      } : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
at (shell):1:9
    2015-11-24T10:46:38.770-0800 E QUERY    Error: command failed: {
    "errmsg" : "exception: can't convert from BSON type String to Date",
    "code" : 16006,
    "ok" : 0
     } : aggregate failed
at Error (<anonymous>)
at doassert (src/mongo/shell/assert.js:11:14)
at Function.assert.commandWorked (src/mongo/shell/assert.js:254:5)
at DBCollection.aggregate (src/mongo/shell/collection.js:1278:12)
at (shell):1:9 at src/mongo/shell/assert.js:13

任何建议我哪里错了。

Answer 1

Mongo在错误消息中告诉你是正确的-它看到的是字符串，而不是日期。

"2015-11-23T01:58:52.084Z"是一个字符串。如果这是一个日期，它应该是ISODate("2015-11-23T01:58:52.084Z")。

您需要将数据更改为日期格式。

Answer 2

要使用$dayOfMonth，您需要将时间戳设置为Date类型。您可以做的一件事是更改为以下代码，以将字符串转换为日期。

db.testing.aggregate([
    {
        $project: {
            day :{$dayOfMonth: new Date("$timeStamp")},
            lessThan10: { $cond: [ { $lt: ["$ObjectTemp", 10 ] }, 1, 0]},
            moreThan10: {$cond:[ {  $gt: [ "$ObjectTemp", 10 ] }, 1, 0]}}
        },
    {
        $group: { 
            _id : "$day",
            countSmaller: { $sum: "$lessThan10" },
            countBigger: { $sum: "$moreThan10" }
        }
    },
    {
        $sort: { _id : -1 }
    }
])

输出：

{ "_id" : 23, "countSmaller" : 0, "countBigger" : 1 }

编辑：它似乎可以工作。也许你检查得不对。我已经用完整的命令和输出更新了答案。

Answer 3

是的我知道。我想出了解决方案。因为这是字符串，所以我不能使用$dayofMonth属性。所以我直接用子字符串表示日期。下面是我的新查询，它工作了：

                     db.temp.aggregate([{
                  $project: {
               day :{ $substr: [ "$timeStamp", 8, 2] },
               lessThan10: { $cond: [ { $lt: ["$ObjectTemp", 10 ] }, 1, 0]},
               moreThan10: {$cond:[ {  $gt: [ "$ObjectTemp", 10 ] }, 1, 0]}}
                 },

                 {
                    $group: { 
                  _id : "$day",
                   countSmaller: { $sum: "$lessThan10" },
                    countBigger: { $sum: "$moreThan10" }
                       }
                    },{$sort: { _id : -1 }}])`