MongoDb如何从字符串按月和年分组 [英] MongoDb How to group by month and year from string

问题描述

我在集合中有字段 dateStr

I am having field dateStr in collection

  {  ....    "dateStr" : "07/01/2020" .... }
  {  ....    "dateStr" : "07/01/1970"   .... }

我想从 dateStr 字段按月和年分组

I want to group by month and year from dateStr field

我试过了

    db.collection.aggregate( 
       {$project : { 
              month : {$month : new Date("$dateStr")}, 
              year : {$year :  new Date("$dateStr")}
          }}, 
        {$group : { 
                _id : {month : "$month" ,year : "$year" },  
              count : {$sum : 1} 
        }})

输出:

{
"result" : [ 
        {
            "_id" : {
                "month" : 1,
                "year" : 1970
            },
            "count" : 2
        }
    ],
    "ok" : 1
}

但我有两年 1970,2020.为什么我得到单曲?

But I am having two years 1970,2020. Why I am getting single record?

推荐答案

您不能将 日期聚合运算符用于任何其他是 Date 对象本身.您最终的最佳选择是将这些字符串"转换为正确的 Date 对象，以便您可以在此操作和将来的操作中正确查询.

You cannot use the date aggregation operators on anything else that is tho a Date object itself. Your ultimate best option is to convert these "strings" to proper Date objects so you can query correctly in this and future operations.

也就是说，如果你的字符串"总是有一个共同的结构，那么有一种方法可以使用 聚合框架 工具.它需要大量的操纵思想，这并不能使这成为处理问题的最佳"方法.但是对于两位数"的集合结构和一致的分隔符，这可以通过 $substr 运算符:

That said, if your "strings" always have a common structure then there is a way to do this with the aggregation framework tools. It requires a lot of manipulation thought that does not makes this an "optimal" approach to dealing with the problem. But with a set structure of "double digits" and a consistent delimiter this is possible with the $substr operator:

db.collection.aggregate([
   { "$group": {
       "_id": {
           "year": { "$substr": [ "$dateStr", 7, 4 ] },
           "month": { "$substr": [ "$dateStr", 4, 2 ] }
       },
       "count": { "$sum": 1 }
   }}
])

所以 JavaScript 转换在聚合框架内不起作用.您始终可以根据客户端代码"评估将输入馈送"到管道，但聚合过程本身不会评估任何代码.就像基本的查询引擎一样，这一切都是基于使用本地运算符"指令来完成工作的数据结构"实现.

So JavaScript casting does not work inside the aggregation framework. You can always "feed" input to the pipeline based on "client code" evaluation, but the aggregation process itself does not evaluate any code. Just like the basic query engine, this is all based on a "data structure" implementation that uses "native operator" instructions to do the work.

您不能在聚合管道中将字符串转换为日期.您应该使用真正的 BSON 日期对象，但如果存在可以以词法顺序"呈现的一致格式，则可以使用字符串.

You cannot convert strings to dates in the aggregation pipeline. You should work with real BSON Date objects, but you can do it with strings if there is a consistent format that you can present in a "lexical order".

我仍然建议您尽快将这些转换为 BSON Dates.请注意，ISODate"或 UTC 值是用不同的字符串形式构造的.即:

I still suggest that you convert these to BSON Dates ASAP. And beware that the "ISODate" or UTC value is constructed with a different string form. ie:

new Date("2020-01-07")

采用yyyy-mm-dd"格式.至少对于 JavaScript 调用而言.

Being in "yyyy-mm-dd" format. At least for the JavaScript invocation.

这篇关于MongoDb如何从字符串按月和年分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！