MongoDB 中的数据类型转换 [英] Data type conversion in MongoDB

问题描述

我在 MongoDB 中有一个名为 Document 的集合.此集合中的文档有一个名为 CreationDate 的字段，以 ISO 日期类型存储.我的任务是统计每天创建的文档数量并按数量异步排序.输出格式必须是[{_id:'yyyy-MM-dd', cnt:x}].我尝试使用如下聚合框架.

I have a collection called Document in MongoDB. Documents in this collection have a field called CreationDate stored in ISO date type. My task is to count the number of documents created per day and sort by the number asynchronously. The output format is required to be [{_id:'yyyy-MM-dd', cnt:x}]. I tried to use aggregation framework as below.

db.Document.aggregate( 
    , {$project: {_id:1, Year:{$year:'$CreationDate'}, Month:{$month:'$CreationDate'}, Date:{$dayOfMonth:'$CreationDate'}}}
    , {$group: {_id:{$concat:['$Year', '-', '$Month', '-', '$Date']}, cnt:{$sum:1}}}
    , {$sort:{'cnt':-1}}
);

代码给了我如下错误:

$concat only supports strings, not NumberInt32

我理解这是因为 $year、$month 和 $dayOfMonth 都返回数字.可以将 _id 字段组合为一个对象，并在应用程序级别将其重新格式化为所需的格式.

I understand this is because $year, $month and $dayOfMonth all return number. It's possible to compose the _id field as an object and re-format it in the desired format in application level.

但从技术角度来看，我有两个问题:

But from technical perspective, I have two questions:

1. 如何在 MongoDB shell 中将数字转换为字符串?在这种情况下，$year 的输出可以转换为字符串并在 $concat 中使用.

1. How to convert a number to string in MongoDB shell? In this case, output of $year can then be converted to string and used in $concat.

是否有更好的方法将 ISODate 输出格式化为各种日期格式?在很多情况下，我们只需要 ISODate 的某些部分，例如:日期组件或时间部分.是否有任何 MongoDb 内置运算符来实现这一目标?

Is there a better way to format ISODate output to various date formats? In many cases, we only need certain part of an ISODate, for example: the date component or the time portion. Is there any MongoDb inbuilt operators to achieve this?

提前感谢您的建议.

推荐答案

您可以使用 $concat 但首先你需要通过 $substr，同样处理两位数的情况:

You can do this with $concat but first you need to convert to a string via $substr, also handling the double digit case:

db.Document.aggregate([ 
    { "$group": { 
        "_id":{ 
            "$concat": [
                 { "$substr": [ { "$year": "$CreationDate" }, 0, 4 ] },
                 "-",
                 { "$cond": [
                     { "$gt": [ { "$month": "$CreationDate" }, 9 ] },
                     { "$substr": [ { "$month": "$CreationDate" }, 0, 2 ] },
                     { "$concat": [
                         "0",
                         { "$substr": [ { "$month": "$CreationDate" }, 0, 1 ] },
                     ]},
                 ]},
                 "-",
                 { "$cond": [
                     { "$gt": [ { "$dayOfMonth": "$CreationDate" }, 9 ] },
                     { "$substr": [ { "$dayOfMonth": "$CreationDate" }, 0, 2 ] },
                     { "$concat": [
                         "0",
                         { "$substr": [ { "$dayOfMonth": "$CreationDate" }, 0, 1 ] },
                     ]}
                 ]}
             ]
         },
         { "cnt": { "$sum": 1 } }
    }}
    { "$sort":{ "cnt" :-1 }}
]);

可能更好的是只使用日期数学，这会返回一个纪元时间戳值，但在后期处理中很容易处理日期对象:

Possibly better is to just use date math instead, this returns an epoch timestamp value, but it is easy to work into a date object in post processing:

db.Document.aggregate([
    { "$group": {
        "_id": {
            "$subtract": [
                { "$subtract": [ "$CreationDate", new Date("1970-01-01") ] },
                { "$mod": [
                    { "$subtract": [ "$CreationDate", new Date("1970-01-01") ] },
                    1000 * 60 * 60 * 24
                ]}
            ]
        },
        "cnt": { "$sum": 1 }
    }},
    { "$sort": { "cnt": -1 } }
])

这篇关于MongoDB 中的数据类型转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！