If you say it works in another client then likely something got lost in the transation. There are things you can certainly clean up here to make it more simplified.
Can I generally suggest a much more efficient "math" approach to determining the current quarter rather than the current nested conditional statements, as if nothing else it does make things a lot cleaner. In addition of "efficiency" you should not be using $project just preceeding a $group where it makes logical sense to simply combine everything into that one stage:
[
{ "$group": {
"_id": {
"year": { "$year": "$dateSold" },
"quarter": {
"$add": [
{ "$subtract": [
{ "$divide": [{ "$subtract": [{ "$month": "$dateSold" },1]},3]},
{ "$mod": [
{ "$divide": [{ "$subtract": [{ "$month": "$dateSold" },1]},3]},
1
]}
]},
1
]
}
},
"unitsSold": { "$sum": "$unitsSold" }
}}
]
By all means add in your "$push": "$$ROOT" if you really must, but cutting down the involved logic and putting everything into a single pipeline stage where it is logical to do so is largely the point here.
The next phase is that I strongly suggest you code this up natively. Whilst it may be tempting to think that you have a JSON notation that you can use, you will find that in time this is neither flexible nor does it provide very good readability to place in long strings and rely on parsing them. Moreover you are generally going to want to interpolate local variables at some stage
Aggregation aggregation = newAggregation(
new CustomGroupOperation(
new BasicDBObject("$group",
new BasicDBObject("_id",
new BasicDBObject("year",new BasicDBObject("$year","$dateSold"))
.append("quarter",new BasicDBObject(
"$add",Arrays.asList(
new BasicDBObject("$subtract",Arrays.asList(
new BasicDBObject("$divide",Arrays.asList(
new BasicDBObject("$subtract",Arrays.asList(
new BasicDBObject("$month","$dateSold"),
1
)),
3
)),
new BasicDBObject("$mod",Arrays.asList(
new BasicDBObject("$divide", Arrays.asList(
new BasicDBObject("$subtract",Arrays.asList(
new BasicDBObject("$month", "$dateSold"),
1
)),
3
)),
1
))
)),
1
)
))
)
.append("unitsSold", new BasicDBObject("$sum", "$unitsSold"))
)
)
);
You also seem to have abstracted some other code, but I personally prefer to implement the CustomGroupOperation in a way that is not going to conflict with using other spring-mongo aggregation helpers within the newAggregation contruction:
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);
}
}
But as originally stated, if you are getting a 0 result then it's field naming or "type" where the field is differently named or in fact a string. But the identical statement should then fail in any other client in a similar way, and the only remedy is to fix the naming or "type" as appropriate.
This is certainly a "cleaner" approach to what you are doing. The "math" is sound for an indexed quarter and can even be adapted to other "financial quarters" as is appropriate via a simple mapping. As does the consolidation of pipeline stages here provide significant performance gains in line with the overall size of the data as a $project means an un-necessary pass through the data just to pre-adjust the fields, which you don't want.
Fix up the definition and execution and then check your fields and data to see that everything is correctly named and typed.
