I have some mongodb problems and need help.
This is the creation statement of the document:
db.getCollection("test").insert( {
_id: ObjectId("5b0cf67270e4fa02d31de42e"),
name: "xiaoming",
time: 400,
mon: NumberDecimal("100.7645234")
} );
db.getCollection("test").insert( {
_id: ObjectId("5b0cf6ad70e4fa02d31de430"),
name: "xiaoming",
time: 0,
mon: NumberDecimal("1.23734324")
} );
db.getCollection("test").insert( {
_id: ObjectId("5b0d14c870e4fa02d31de436"),
name: "xiaoming",
time: 0,
mon: NumberDecimal("1.785233")
});
I hope to get such a result:
name | total_count |
---|---|
xiaoming | 103.79 |
In other words, the mon field in the document needs to be rounded first and then rounded. Each mon field is formatted as a number with two decimal places, and then the final summation operation is carried out.Instead of simply rounding the final sum.
This is the statement I have written so far, but obviously the result is not what I want:
db.test.aggregate([
{
'$group': {
_id: '$name'
}
},
{
'$project': {
total_count: {
'$sum': {
'$divide': [{
'$trunc': {
'$add': [{
'$multiply': ['$mon', 100]
}, 0.5]
}
}, 100]
}
}
}
}
])
The code I write can only achieve this effect:
name | total_count |
---|---|
xiaoming | 0 |
Obviously, the code I wrote was wrong.
help me, thanks you!
CodePudding user response:
Your aggregation is incorrect, the $project
stage is performed after the $group
stage and therefore only has access to the output document from the $group
stage which does not contain the $mon
field. Aggregates can be calculated within the same $group
stage. Also, note that MongoDB provides a $round
expression that simplifies the aggregation expression:
db.collection.aggregate([
{
"$group": {
_id: "$name",
total_count: {
"$sum": {
"$round": [
"$mon",
2
]
}
}
}
}
])
try it in mongo playground
Your original expression works as well, if executed within the $group
instead of the $project
stage:
db.collection.aggregate([
{
"$group": {
_id: "$name",
total_count: {
"$sum": {
"$divide": [
{
"$trunc": {
"$add": [
{
"$multiply": [
"$mon",
100
]
},
0.5
]
}
},
100
]
}
}
}
}
])
CodePudding user response:
Query
- try this its your code, just inside an accumulator
aggregate(
[{"$group":
{"_id": "$name",
"count":
{"$sum":
{"$divide":
[{"$trunc": {"$add": [{"$multiply": ["$mon", 100]}, 0.5]}}, 100]}}}},
{"$set": {"name": "$_id"}},
{"$project": {"_id": 0}}])