Aggregation Project Group By Day, Month, and Year Results Using Spring Data

Question

To be direct how to do this:

group._id = { year: { $year : [{ $subtract: [ "$timestamp", 25200000 ]}] }, month: { $month : [{ $subtract: [ "$timestamp", 25200000 ]}] }, day: { $dayOfMonth : [{ $subtract: [ "$timestamp", 25200000 ]}] } }; 

with spring data

I tried this already and some other forms and was not successful

 Aggregation aggregation = Aggregation.newAggregation( Aggregation.match(c), Aggregation.project("programa", "custo", "duracao", "dataHora") .andExpression("dataHora").minus(25200000).extractDayOfMonth().as("dia") .andExpression("dataHora").minus(25200000).extractMonth().as("mes") .andExpression("dataHora").minus(25200000).extractYear().as("ano"), Aggregation.group("programa", "ano", "mes", "dia") .count().as("count") .sum("custo").as("valorTotal") .sum("duracao").as("duracaoTotal") .first("dataHora").as("dataHora"), Aggregation.sort(Direction.ASC, "dataHora") ); 

I need to group by days, months and years in mongodb, otherwise I will need to convert all this grouped data into code.

Thank you in advance

Answer 1

You use the spring mongo limitation in what you can do to calculate the fields at one stage of the $project , and you most likely already write as a separate $project , since you find that you cannot project custom name fields directly into $group _id now.

So, it would be better for you to save all this in $group , and also use a different method of rounding the set dates according to local time.

Thus, the best way to write $group :

 { "$group": { "_id": { "programa": "$programa", "dataHora": { "$add": [ { "$subtract": [ { "$subtract": [{ "$subtract": ["$dataHora", new Date(0)] }, 25200000 ] }, { "$mod": [ { "$subtract": [{ "$subtract": ["$dataHora", new Date(0)] }, 25200000 ] }, 1000 * 60 * 60 * 24 ]} ]}, new Date(0) ] } }, "count": { "$sum": 1 }, "valorTotal": { "$sum": "$custo" }, "duracaoTotal": { "$sum": "$duracao" }, "dataHora": { "$first": "$dataHora" } }} 

Of course, to use this type of structure with spring-mongo, you need an individual implementation of the aggregation step operation, which can take certain 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); } } 

which you then use in context as follows:

  Aggregation aggregation = Aggregation.newAggregation( Aggregation.match(c), new CustomGroupOperation( new BasicDBObject("$group", new BasicDBObject("_id", new BasicDBObject("programa","$programa") .append("dataHora", new BasicDBObject("$add",Arrays.asList( new BasicDBObject("$subtract",Arrays.asList( new BasicDBObject("$subtract",Arrays.asList( new BasicDBObject("$subtract",Arrays.asList( "$dataHora", new Date(0) )), 25200000 )), new BasicDBObject("$mod",Arrays.asList( new BasicDBObject("$subtract",Arrays.asList( new BasicDBObject("$subtract",Arrays.asList( "$dataHora", new Date(0) )), 25200000 )), 1000 * 60 * 60 * 24 )) )), new Date(0) )) ) ) .append("count",new BasicDBObject("$sum",1)) .append("valorTotal",new BasicDBObject("$sum","$custo")) .append("duracaoTotal",new BasicDBObject("$sum","$duracao")) .append("dataHora",new BasicDBObject("$first","$dataHora")) ) ), Aggregation.sort(Direction.ASC,"_id.dataHora") ); 

Since theses are a custom class from the same base class that is used by the built-in helper methods, it can be used next to it, as shown.

How does the main process with date math work, when you $subtract one BSON date object from another, then the result is milliseconds of difference, and in this case, from an epoch date (Date (0)), which simply retrieves the value of milliseconds. This allows you to perform math with rounding to the current date modulo ( $mod ) from the number of milliseconds in one day.

As you originally tried, when you $add that the millisecond value for the Date BSON object, the return value is again the BSON date. Therefore, adding an object representing an era returns a new Date object, but is rounded to the current date.

This is usually much more useful than retrieving parts using date aggregation operators , and it also gets a little shorter than the code, especially when setting the time with UTC, as you do here.

Although the $group construction is a little more complicated here than the spring mongo helper functions are trying to avoid, in the end it is much more efficient than running a separate $project step to convert the values ​​of the fields that you really need only at the $group stage.