I'm trying to group a set of documents and count them based on their value:
{ item: "abc1", value: 1 }
{ item: "abc1", value: 1 }
{ item: "abc1", value: 11 }
{ item: "xyz1", value: 2 }
I would like to group by item and get in return a count of how many times the value is bigger than 10 and how many times smaller:
{ item: "abc1", countSmaller: 2, countBigger: 1 }
{ item: "xyz1", countSmaller: 1, countBigger: 0 }
What you need is the $cond operator of aggregation framework. One way to get what you want would be:
db.foo.aggregate([
{
$project: {
item: 1,
lessThan10: { // Set to 1 if value < 10
$cond: [ { $lt: ["$value", 10 ] }, 1, 0]
},
moreThan10: { // Set to 1 if value > 10
$cond: [ { $gt: [ "$value", 10 ] }, 1, 0]
}
}
},
{
$group: {
_id: "$item",
countSmaller: { $sum: "$lessThan10" },
countBigger: { $sum: "$moreThan10" }
}
}
])
Note: I have assumed value to numeric rather than String.
Output:
{
"result" : [
{
"_id" : "xyz1",
"countSmaller" : 1,
"countBigger" : 0
},
{
"_id" : "abc1",
"countSmaller" : 2,
"countBigger" : 2
}
],
"ok" : 1
}
You need to use the $cond operator. Here 0 is value less than 10 and 1 value greater than 10. This doesn't exactly give you expected output. Perhaps someone will post better answer.
db.collection.aggregate(
[
{
"$project":
{
"item": 1,
"value":
{
"$cond": [ { "$gt": [ "$value", 10 ] }, 1, 0 ]
}
}
},
{
"$group":
{
"_id": { "item": "$item", "value": "$value" },
"count": { "$sum": 1 }
}
},
{
"$group":
{
"_id": "$_id.item",
"stat": { "$push": { "value": "$_id.value", "count": "$count" }}
}
}
]
)
Output:
{
"_id" : "abc1",
"stat" : [
{
"value" : 1,
"count" : 2
},
{
"value" : 0,
"count" : 2
}
]
}
{ "_id" : "xyz1", "stat" : [ { "value" : 0, "count" : 1 } ] }
You will need to convert your value to integer or float
If anyone is looking for Java code for this scenario(fields updated as per my need):
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.project("environment").and("success").applyCondition(ConditionalOperators.when(Criteria.where("deploymentStatus").is("SUCCESS"))
.then(1)
.otherwise(0)).and("failed").applyCondition(ConditionalOperators.when(Criteria.where("deploymentStatus").is("FAILURE"))
.then(1)
.otherwise(0)),
Aggregation.group("environment").sum("success").as("success").sum("failed").as("failed"));
To count data based on the condition in the $group stage we can make use of "$accumulator" operator which is changed in MongoDb version 5.0
So based on your requirement we can implement using this aggregation stage -
db.products.aggregate([
{
$group: {
_id: "$item",
totalCounts: { $sum: 1 },
countsMeta: {
$accumulator: {
init: function () {
// Set the initial state
return { countSmaller: 0, countBigger: 0 };
},
accumulate: function (state, value) {
// Define how to update the state
return value < 10
? { ...state, countSmaller: state.countSmaller + 1 }
: { ...state, countBigger: state.countBigger + 1 };
},
accumulateArgs: ["$value"], // Pass the desired argument to the accumulate function
merge: function (state1, state2) {
/*
Executed when the operator performs a merge,
Merge may happen in two cases :
1). $accumulator is run on a sharded cluster. The operator needs to merge the
results from each shard to obtain the final result.
2). A single $accumulator operation exceeds its specified memory limit.
If you specify the allowDiskUse option, the operator stores the
in-progress operation on disk and finishes the operation in memory.
Once the operation finishes, the results from disk and memory are
merged together using the merge function.
The merge function always merges two states at a time. In the event that more
than two states must be merged, the resulting merge of two states is merged
with a single state. This process repeats until all states are merged.
*/
return {
countSmaller: state1.countSmaller + state2.countSmaller,
countBigger: state1.countBigger + state2.countBigger,
};
},
finalize: function (state) {
// After collecting the results from all documents,
return state;
},
lang: "js",
},
},
},
},
]);
This execution gives the following result
For more information about stage and operator refer the following link
https://www.mongodb.com/docs/manual/reference/operator/aggregation/accumulator/
Hope this will help you or somebody else. Thanks!
Happy Coding :-)
Very similar to Anand's answer, but with one step:
$group: {
_id: "$item",
countSmaller: { $sum: {$cond: [ { $lt: ["$value", 10 ] }, 1, 0]} },
countBigger: { $sum: {$cond: [ { $gt: [ "$value", 10 ] }, 1, 0]} }
}
