mongo组和有条件的计数

I'm trying to group a set of documents and count them based on their value. For example

我正在尝试对一组文档进行分组,并根据它们的值来计算它们。例如

{ "_id" : 1, "item" : "abc1", "value" : "1" }
{ "_id" : 2, "item" : "abc1", "value" : "1" }
{ "_id" : 3, "item" : "abc1", "value" : "11" }
{ "_id" : 4, "item" : "abc1", "value" : "12" }
{ "_id" : 5, "item" : "xyz1", "value" : "2" }

Here I would like to group by "item" and get in return a count how many times the "value" is bigger than 10 and how many times smaller. So:

在这里,我想按“项目”进行分组,并获得一个计数“数值”大于10的次数和小的次数。所以:

{ "item": "abc1", "countSmaller": 2, "countBigger": 1}
{ "item": "xyz1", "countSmaller": 1, "countBigger": 0}

A plain count could be easily achieved with $aggregate, but how can I achieve the above result?

使用$ aggregate可以很容易地实现普通计数,但是如何实现上述结果呢?

3 个解决方案

#1

What you need is the $cond operator of aggregation framework. One way to get what you want would be:

你需要的是聚合框架的$ cond运算符。获得你想要的东西的一种方法是:

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
}

#2

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.

您需要使用$ cond运算符。这里0是小于10的值,1是大于10的值。这并不能完全给出预期的输出。也许有人会发布更好的答案。

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

您需要将值转换为整数或浮点数

#3

If anyone is looking for Java code for this scenario(fields updated as per my need):

如果有人正在为这种情况寻找Java代码(根据我的需要更新字段):

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"));

#1