• Data type conversion in MongoDB


    [问题]

    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}}

    );

    The code gives me error as below:

    $concat only supports strings, not NumberInt32

    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. 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.
    2. 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.

     [回答]

    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 } }
    ])

    来自: https://stackoverflow.com/questions/25176855/data-type-conversion-in-mongodb

  • 相关阅读:
    【荐2】Total Commander 7.57 配置选项 个性化设置备份,,,开启时如何自动最大化???(二)
    【荐1】Total Commander 7.57 个人使用设置 及 常用快捷键 备忘
    Total Commander 集成、调用 Beyond Compare比较文件
    Linux/CentOS下开启MySQL远程连接,远程管理数据库
    如何查看、修改Linux的系统时间
    Linux cp (复制)命令简介
    Linux网络下载命令 wget 简介
    Linux如何下解压windows下的.zip和.rar文件
    Linux tar (打包.压缩.解压缩)命令说明 | tar如何解压文件到指定的目录?
    解决宿主机不能访问虚拟机CentOS中的站点 | 更新CentOS防火墙设置开启80端口访问
  • 原文地址:https://www.cnblogs.com/time-is-life/p/9328688.html
Copyright © 2020-2023  润新知