mongo group and count with condition
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