mongodb如何查询总和字符串? [英] mongodb how to query sum string?

问题描述

我在order文档中有一些数据，例如:

I have some data in order document like:

{ "_id": "...", "orderTotal": { "amount" : "10.99", "unit": "USD"}, "orderTime": "...", ... }
{ "_id": "...", "orderTotal": { "amount" : "9.99", "unit": "USD"}, "orderTime": "...",  ... }
{ "_id": "...", "orderTotal": { "amount" : "8.99", "unit": "USD"}, "orderTime": "...",  ... }

我想按天查询所有订单组的orderTotal:

I want to query the orderTotal of all orders group by day:

db.getCollection('order').aggregate([
  {
    '$group' : {
        '_id': { day: { $dayOfMonth: "$orderTime"}, month: {$month: "$orderTime"}, year: { $year: "$orderTime" }},
        'totalAmount': { $sum: '$itemTotal.amount' },
        'count': { $sum: 1 }
    }
  }
])

但是得到了

{
    "_id" : {
        "day" : 12,
        "month" : 12,
        "year" : 2016
    },
    "totalAmount" : 0,
    "count" : 4607.0
}

amount是一个字符串.使用parseFloat，但得到NaN.

the amount is a string. Use parseFloat but got NaN.

db.getCollection('order').aggregate([
  {
    '$group' : {
        '_id': { day: { $dayOfMonth: "$orderTime"}, month: {$month: "$orderTime"}, year: { $year: "$orderTime" }},
        'totalAmount': { $sum: parseFloat('$itemTotal.amount') },
        'count': { $sum: 1 }
    }
  }
])

得到

{
    "_id" : {
        "day" : 12,
        "month" : 12,
        "year" : 2016
    },
    "totalAmount" : NaN,
    "count" : 4607.0
}

我无法更新order文档以像其他问题一样将itemTotal.amount更改为浮动:

I can't update the order document to change itemTotal.amount to float like other questions said:

db.order.find().forEach(function(data) {
    db.order.update({
        "_id": data._id,
        "itemTotal.amount": data.itemTotal.amount
    }, {
        "$set": {
            "itemTotal.amount": parseFloat(data.itemTotal.amount)
        }
    });
})

我无权执行此操作. 那么，如何按天计算总和?

I have no permissions to do this. So, how can I get the sum by day?

推荐答案

从MongoDB 3.4开始，这是不可能的.已请求此功能，但尚未实现:

This is not possible as of MongoDB 3.4. This feature has already been requested, but hasn't been implemented yet:

需要一种类型转换机制来在字符串和数字之间进行转换

所以解决问题的唯一方法是手动执行javascript中的totalAmount sum ...

So the only way to solve your problem is to manually perform the totalAmount sum in javascript...

现在可以在引入了运算符的 MongoDB 4.0 中将其从一种类型转换为另一种类型，例如 $toDouble

This is now possible in MongoDB 4.0 wich introduced operator to convert from one type to another, for example $toDouble

因此查询将是:

db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "totalAmount": {
        "$sum": {
          "$toDouble": "$orderTotal.amount"
        }
      },
      "count": {
        "$sum": 1
      }
    }
  }
])

您可以在这里尝试: mongoplayground.net/p/4zJTPU912Es

这篇关于mongodb如何查询总和字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！