按条件分组并求和

Question

我使用mongoTemplate查询我的mongodb数据库，并且想在我的集合中进行一些计数。我希望按id分组并包含满足条件的计数。我在mongoshell中使用了这个查询：

db.scenarios.aggregate([
    { $match: { bid: "build_1481711758" } },
    {
        $group: {
            _id: "$bid",
            nb: { $sum: 1 },
            nbS: {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$scst",  true ] },  
                        1, 0 
                    ]
                }
            },
            nbE: {
                "$sum": {
                    "$cond": [
                        { "$eq": ["$scst",  false ] },  
                        1, 0 
                    ]
                }
            }
        }
    }
])

我已经得到了想要的结果，但我不知道如何将其转换为Java MongoTemplate。

请帮忙：）

- Khairy

Answer 1

您可以简化管道到

db.scenarios.aggregate([
    { $match: { bid: "build_1481711758" } },
    {
        $group: {
            _id: "$bid",
            nb: { $sum: 1 },
            nbS: {
                "$sum": {
                    "$cond": [ "$scst",  1, 0 ]
                }
            },
            nbE: {
                "$sum": {
                    "$cond": [ "$scst",  0, 1 ]
                }
            }
        }
    }
])

由于$cond运算符评估布尔表达式以返回两个指定的返回表达式之一，而scst字段默认返回布尔值。

如果使用当前Spring Data版本，该版本通过$project管道支持$cond运算符，则可以将其转换为（未经测试）：

import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;
import static org.springframework.data.mongodb.core.aggregation.ConditionalOperators.Cond.*;
import org.springframework.data.mongodb.core.query.Criteria;

Cond operatorNbS = ConditionalOperators.when("scst").thenValueOf(1).otherwise(0);
Cond operatorNbE = ConditionalOperators.when("scst").thenValueOf(0).otherwise(1);

Aggregation agg = newAggregation(
    match(Criteria.where("bid").is("build_1481711758"),
    project("bid") 
        .and("scst")                            
        .applyCondition(operatorNbE, field("nbE"))
        .applyCondition(operatorNbS, field("nbS"))
    group("bid")
        .count().as("nb")
        .sum("nbE").as("nbS")
        .sum("nbE").as("nbE") 
);
AggregationResults<Scenarios> results = mongoTemplate.aggregate(agg, Scenarios.class); 
List<Scenarios> scenarios = results.getMappedResults();

---

如果你的Spring Data版本不支持，一种解决方法是实现AggregationOperation接口以接收DBObject：

public class CustomGroupOperation implements AggregationOperation {
    private DBObject operation;

    public CustomGroupOperation (DBObject operation) {
        this.operation = operation;
    }

    @Override
    public DBObject toDBObject(AggregationOperationContext context) {
        return context.getMappedObject(operation);
    }
}

然后将 $group 操作作为聚合管道中的 DBObject 实现，与您拥有的相同：

DBObject operation = (DBObject)new BasicDBObject(
    "$group", new BasicDBObject(
        "_id", "$bid"
    )
    .append( "nb", new BasicDBObject("$sum", 1) )
    .append(
        "nbS", new BasicDBObject(
            "$sum", new BasicDBObject(
                "$cond", new Object[]{ "$scst", 1, 0 }
            )
        )
    ).append(
        "nbE", new BasicDBObject(
            "$sum", new BasicDBObject(
                "$cond", new Object[]{ "$scst", 0, 1 }
            )
        )
    )
);

然后您可以将其用作：

Aggregation agg = newAggregation(
    match(Criteria.where("bid").is("build_1481711758"),
    new CustomGroupOperation(operation)
);

---

考虑采用以下替代流水线，以获得更灵活、性能更佳且执行速度比上述方法快得多的效果。

 db.scenarios.aggregate([
    { $match: { bid: "build_1481711758" } },
    { 
        "$group": {
            "_id": { 
                "bid": "$bid",
                "scst": "$scst"
            },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.bid",
            "counts": {
                "$push": {
                    "scst": "$_id.scst",
                    "count": "$count"
                }
            }
        }
    }
])

- chridam