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?
To get sum the value of a key across all documents in a MongoDB collection, you can use aggregate().
$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.
MongoDB count() Method – db. Collection. count() The count() method counts the number of documents that match the selection criteria.
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.
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...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With