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

问题描述

我在MongoDB中有一个名为Document的集合.此集合中的文档具有一个存储在ISO日期类型中的称为CreationDate的字段.我的任务是计算每天创建的文档数量，然后异步按数量排序.输出格式必须为 [{_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?

在此先感谢您的任何建议.

Thanks in advance for any advice.

推荐答案

您可以使用 $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屋！