时间戳

首页 > 解决方案 > 将字符串转换为数字并计算mongodb中数组中值的总和

mongodb - 将字符串转换为数字并计算mongodb中数组中值的总和

问题描述

我想用数组中的类型字符串对总金额求和，所以我在 mongo 中写了这个。请有任何帮助

试过：

db.compte.aggregate([
 {$project:{
   account: "$auditMessages.eventMetaData.Counterparty Name",
   amount: "$auditMessages.eventMetaData.Transaction Amount",
   "total": {$sum:"$amount"}
 }}])

结果：

{
"_id" : ObjectId("5ed638e566a699750cbb1b0f"),
"account" : [
  "Mr Jalel CHAHED",
  "Mr Jalel CHAHED",
  "Mr Jalel CHAHED",
  "Mr Jalel CHAHED"
 ],
"amount" :
 [ 
  "4.32",
  "4.32",
   "1.32",
   "1.32"
 ],
"total" : 0}

标签： mongodbmongodb-queryaggregation-framework

解决方案

您正在创建amount字段$project并在同一阶段使用它本身，这就是它返回的原因0，"total": {$sum:"$amount"}似乎$sum也不采用数组。

假设您的文件如下所示：

{
    "auditMessages": {
      eventMetaData: [
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 1,
          "Transaction Amount": "4.32"
        },
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 11,
          "Transaction Amount": "4.32"
        },
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 12,
          "Transaction Amount": "1.32"
        },
        {
          "Counterparty Name": "Mr Jalel CHAHED",
          abc: 122,
          "Transaction Amount": "1.32"
        }
      ]
    }
  }

就像auditMessages.eventMetaData一个数组一样，当你这样做时：

"$auditMessages.eventMetaData.Counterparty Name"或者"$auditMessages.eventMetaData.Transaction Amount"你得到的是一个值数组，就像你现在在结果中看到的一样。

询问 ：

db.collection.aggregate([
    {
      $project: {
        account: { $arrayElemAt: [ "$auditMessages.eventMetaData.Counterparty Name", 0 ] }, // get name from first element in array,use only if name is same in all objects
        total: {
          $reduce: {
            input: "$auditMessages.eventMetaData.Transaction Amount", // Iterate on array
            initialValue: 0,
            in: { $trunc: [ { $add: [ "$$value", { $toDouble: "$$this" } ] }, 2 ] } // convert string to double, sum-up & truncate to 2 digits
          }
        }
      }
    }
  ])

测试： mongoplayground

参考： 聚合

推荐阅读