how to use sum and condition in group using spring data mongodb aggregation

aggregation-framework, mongodb, spring-data-mongodb

Solution

You can use the below aggregation pipeline.

import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;
import static org.springframework.data.mongodb.core.aggregation.ArithmeticOperators.*;
import static org.springframework.data.mongodb.core.aggregation.ConditionalOperators.when;
import static org.springframework.data.mongodb.core.query.Criteria.where;

Aggregation aggregation = 
           newAggregation(
               project("id").
                  and(when(where("status").is("A")).then(1).otherwise(0)).as("status").
                  and(Subtract.valueOf("end_time").subtract("start_time")).as("diffTime"),
               group("$id").count().as("total").sum("status").as("live").avg("diffTime").as("chat_hrs"));

Generated Mongo Query:

[{
    "$project": {
        "id": 1,
        "status": {
            "$cond": {
                "if": {
                    "$eq": ["$status", "A"]
                },
                "then": 1,
                "else": 0
            }
        },
        "diffTime": {
            "$subtract": ["$end_time", "$start_time"]
        }
    }
}, {
    "$group": {
        "_id": "$id",
        "total": {
            "$sum": 1
        },
        "live": {
            "$sum": "$status"
        },
        "chat_hrs": {
            "$avg": "$diffTime"
        }
    }
}]

Problem

Original source