MongoTemplate : group by on multiple fields with condition clause

Question

Earlier I had below mentioned aggregation pipeline.

db.EXCEPTIONS.aggregate(
[{ $match : {
        $and : [
            {workflow_stage_current_assignee : {$ne:null}},
            {CreatedDate:{$gt:ISODate("2017-07-12")}}, 
            {CreatedDate:{$lt:ISODate("2018-07-12")}} 
        ]}
 },
 {
     $group : {_id: {user : "$workflow_stage_current_assignee", cm:"$control_monitor"},
               exceptions:{$sum:1} }   
 },
 { $project : {"user":1,"cm":1,"exceptions":1}},
 { $sort : {"exceptions":1}}

]);

And corresponding conversion in Mongo template is as below.

Calendar fromDate = Calendar.getInstance();
fromDate.set(2016, 7, 12, 0, 0, 0);

Calendar toDate = Calendar.getInstance();
toDate.set(2018, 7, 12, 23, 59, 59);

MatchOperation match = Aggregation.match(new Criteria("workflow_stage_current_assignee").ne(null)
        .andOperator(new Criteria("CreatedDate").gte(new Date(fromDate.getTimeInMillis()))
        .andOperator(new Criteria("CreatedDate").lte(new Date(toDate.getTimeInMillis())))));

GroupOperation group = Aggregation.group("workflow_stage_current_assignee","control_monitor").count().as("exceptions");

ProjectionOperation project = Aggregation.project("workflow_stage_current_assignee","control_monitor","exceptions");

SortOperation sort=Aggregation.sort(Sort.Direction.DESC,"exceptions");

Aggregation aggregation  = Aggregation.newAggregation(match,group,project,sort);

AggregationResults<ExceptionCount> output  = mongoTemplate.aggregate(aggregation, "EXCEPTIONS", ExceptionCount.class);

This worked fine. Then I modified original query as below and added additional grouping option as overdue.

$group : {_id: {user : "$workflow_stage_current_assignee", cm:"$control_monitor"},
               exceptions:{$sum:1},
               },
               overdue: {$sum : {$cond :[ 
                                    {$gt:["$CreatedDate",ISODate("2017-07-12")]},1,0
                                ]}
               }

But I am not aware how to achieve this additional group clause in Mongo Template queries. I searched on internet but most of the results use DBObject from older API. Can any one help me in this with Mongo Template?

Solution

Finally I found the answer!

Since I couldn't find a way to conditionally group the fields using Mongo template, I had to tweak the logic of aggregation pipeline itself.

1. I used conditional projection to project an additional field.
2. Performed projection operation first.
3. Then performed grouping using this additional field.

So code changes required are as below.

Step 1

I modified my projection clause and added another conditional projection field as below.

Cond overdueCondition = ConditionalOperators.when(new Criteria("CreatedDate").gt(new Date())).then(1).otherwise(0);
ProjectionOperation project = Aggregation.project("workflow_stage_current_assignee","control_monitor","exceptions").and("overdue").applyCondition(overdueCondition);

The result of this operation would reduce my result set to something like this:

{
"workflow_stage_current_assignee" : "Yogesh",
"control_monitor" : "P005",
"exceptions":"",
"overdue" : 1
},
{
"workflow_stage_current_assignee" : "Yogesh",
"control_monitor" : "P005",
"exceptions":"",
"overdue" : 0
}...

After step 1, I got another field in projection called overdue, with value either 1 or 0 depending on if my condition is satisfied or not.

Step 2

Then in group clause I used Sum operator to add all such overdue fields.

GroupOperation group =  Aggregation.group("workflow_stage_current_assignee","control_monitor").count().as("exceptions").sum("overdue").as("overdue");

Step 3

And finally I modified my aggregation pipeline to perform Projection operation first and then group all the results.

Aggregation aggregation  = Aggregation.newAggregation(match,project,group,sort);

This gave me required results!

Comments

[s7vr]

Possible duplicate of How to express complex $sum grouping expression in Spring Data MongoDB

[Yogesh Pitale]

I feel I had asked question before the question in above link. So that should be duplicate of this question & not vice versa! :)

[s7vr]

Not sure what the rules are. That answer includes the latest update where you can pass the conditional operator as a expression to the sum operator without needing extra project stage.