时间戳

首页 > 解决方案 > How do I Convert a subset of fields in all documents to $toDouble?

mongodb - How do I Convert a subset of fields in all documents to $toDouble?

问题描述

I have a database with many documents structured like so:

Date:2021-01-02T08:00:00.000+00:00
FIT_ME44_Volume:"984385"
LT_MJ01:"0"
LT_MJ08:"0"
LT_MJ16:"-34.526024"
FIT_ME56_Volume:"0"
FIT_MJ22_Volume:"9538598"
LT_MJ26:"-61.803848"
FIT_ME52_Volume:"2734271"
LT_ME16:"0"
FIT_MJ28_Volume:"0"
LT_ME29:"2.10552"
LT_ME02:"2.005206"
LT_ME50:"8.732683"
FIT_MJ13_Volume:"0"
FIT_ME02_Volume:"1131376"
FIT_ME23_Volume:"2585415"
LT_ME03:"6.918576"
FIT_MJ08_Volume:"0"
FIT_MJ18_Volume:"0"
QQCr_Total_Plant:"5471052"
FIT_ME03_Volume:"103164"
FIT_ME51_Volume:"3587575"
LT_ME06:"24.423439"
FIT_ME46_Volume:"1619"

What I would like to do is convert all fields in all documents except Date from string to double. I can do this on a field by field basis, but how do I accomplish this in bulk?

标签： mongodbtype-conversionfieldmultiple-columns

解决方案

您可以尝试从 MongoDB 4.2 开始使用聚合管道查询进行更新，

  • $objectToArray将根文档从对象转换为对象格式的键值数组
  • $map迭代上述转换数组的循环
  • k按原样返回键
  • $cond检查提供的数组中的键然后不转换意味着忽略并返回现有值，如果不想转换也可以添加字段名称
  • 否则$toDouble将字符串值转换为双精度
  • $arrayToObject返回将上面的对象键值数组转换为真实对象格式
  • $replaceRoot将上面转换的对象替换为根
db.collection.updateMany(
  {},
  [{
    $replaceRoot: {
      newRoot: {
        $arrayToObject: {
          $map: {
            input: { $objectToArray: "$$ROOT" },
            in: {
              k: "$$this.k",
              v: {
                $cond: [
                  { $in: ["$$this.k", ["Date", "_id"]] },
                  "$$this.v",
                  { $toDouble: "$$this.v" }
                ]
              }
            }
          }
        }
      }
    }
  }]
)

操场

推荐阅读