Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mongodb how to query sum string?

Tags:

mongodb

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": "...",  ... }

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

but got:

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

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

got

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

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?

like image 799
heramerom Avatar asked Dec 13 '16 11:12

heramerom


People also ask

How do you sum values in MongoDB?

To get sum the value of a key across all documents in a MongoDB collection, you can use aggregate().

How does MongoDB calculate sum of salary?

$sum operator returns the sum of all numeric values of documents in the collection in MongoDB. Above will create collection (or table) (if collection already exists it will insert documents in it). Step 2.1 - We will group employee by firstName and find sum of salary of each group.

How can I get total number in MongoDB?

MongoDB count() Method – db. Collection. count() The count() method counts the number of documents that match the selection criteria.

Can we use count with aggregate function in MongoDB?

MongoDB $count AggregationThe MongoDB $count operator allows us to pass a document to the next phase of the aggregation pipeline that contains a count of the documents. There a couple of important things to note about this syntax: First, we invoke the $count operator and then specify the string.


1 Answers

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

Need a type conversion mechanism to convert between strings and numbers

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


Edit

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

so the query would be:

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

you can try it here: mongoplayground.net/p/4zJTPU912Es

like image 178
felix Avatar answered Oct 18 '22 02:10

felix