MongoDB汇总未按日期对文档进行分组 [英] Mongodb aggregate not grouping documents by date

问题描述

我的MongoDB中有以下3个文档:

I have the following 3 documents in my MongoDB:

[Object_id: "tMSfNq9JR85XDaQe5"date: Sun Dec 07 2014 19:50:21 GMT+0800 (HKT)description: "Test" projectid: "S83NEGHnrefvfASrf"totalseconds: 22200__proto__: Object, 

Object_id: "FeyzdMosaXCht8DKK"date: Mon Dec 15 2014 00:00:00 GMT+0800 (HKT)description: "444"projectid: "S83NEGHnrefvfASrf"totalseconds: 3660__proto__: Object, 

Object_id: "cCKByxSdQMHAsRKwd"date: Mon Dec 15 2014 00:00:00 GMT+0800 (HKT)description: "555"projectid: "S83NEGHnrefvfASrf"totalseconds: 3660__proto__: Object]

我正在尝试在其上运行以下聚合管道以将totalseconds的总和与date分组，以便最终结果将如下所示，但是每次我得到的结果都是3条记录时，作为输入的3个文档...有人可以告诉我我可能做错了什么/在这里丢失了吗?谢谢

I am trying to run the following aggregate pipeline on it to group the sum of totalseconds by date so that the end result will be something like shown below, yet each time I get the result as 3 records exactly just as input 3 documents...can someone please tell me what I might be doing wrong / missing here? Thanks

var pipeline = [
  {$group:
        {_id:{"projectId":"$projectid", "date":"$date"},
         totalHrs:{$sum:"$totalseconds"}
        }
  }
];


 { "Date":"Sun Dec 07 2014 19:50:21 GMT+0800 (HKT)",
   "totalseconds": "22200"
 },
 { "Date":"Sun Dec 15 2014 00:00:00 GMT+0800 (HKT)",
   "totalseconds": "7320"
 }

推荐答案

您在此处提供的所有内容都是现有的日期"值，它是分组键的一部分.它就是它所说的，并且可能非常细微，即毫秒.

All you are supplying here is the existing "date" value as part of the grouping key. It is exactly what it says it is and is likely to be very granular, i.e to the millisecond.

这里想要的只是在日期类型字段中使用记录的时间戳值的天"部分.为此，您可以使用聚合框架的日期运算符:

What you want here is to just use the "day" part of the recorded timestamp value in your date type field. For this you can use the date operators of the aggregation framework:

db.collection.aggregate([
    { "$group": {
        "_id": { 
            "projectid": "$projectid",
            "day": { "$dayOfMonth": "$date" },
            "month": { "$month": "$date" },
            "year": { "$year": "$date" }
        },
        "totalseconds": { "$sum": "$totalseconds" }
    }}
])

或者仅在日期对象上使用日期数学并将值舍入为一天:

Or just use date math on your date objects and round the values to a day:

db.collection.aggregate([
    { "$group": {
        "_id": { 
            "projectid": "$projectid",
            "date": {
                "$subtract": [
                    { "$subtract": [ "$date", new Date("1970-01-01") ]},
                    { "$mod": [
                        { "$subtract": [ "$date", new Date("1970-01-01") ]},
                        1000 * 60 * 60 * 24
                    ]}
                ]
            }
        },
        "totalseconds": { "$sum": "$totalseconds" }
    }}
])

无论哪种方式，您都只需要日期字段的一部分，而不是全部.否则，除非确切的时间值相同，否则实际上没有任何分组依据.

Either way, you want just part of the date field and not all of it. Otherwise there is nothing to actually group on unless exact time values are the same.

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