Grouping and Sum with Conditions

Question

I use mongoTemplate to query my mongodb database, and I want to do some counting in my collections. I want to group by id and include counting with conditions. I used this query in 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 
                    ]
                }
            }
        }
    }
])

and it returns what I want, but I dont know how to convert it to java mongotemplate.

Please help :)

Answer 1

You can simplify the pipeline to

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 ]
                }
            }
        }
    }
])

since the $cond operator evaluates a boolean expression to return one of the two specified return expressions and the scst field by default returns a boolean.

If using the current Spring Data release which has support for the $cond operator via the $project pipeline, then this can be converted to (untested):

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

---

If your Spring Data version does not support this, a workaround is to implement the AggregationOperation interface to take in a 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);
    }
}

Then implement the $group operation as a DBObject in the aggregation pipeline that is the same as the one you have:

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

which you can then use as:

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

---

For a more flexible and better performant approach which executes much faster than the above, consider running an alternative pipeline as follows

 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"
                }
            }
        }
    }
])