I'm trying to create an aggregate query using mongotemplate where there's a grouping by date (i.e 2016-03-01) instead of datetime (i.e 2016-03-01 16:40:12).
The dateToString
operation exists in the mongodb documentation it can be used to extract the date from the datetime using formatting:
https://docs.mongodb.org/manual/reference/operator/aggregation/dateToString/
but I get get it to work with mongotemplate - I get a NullPointerException.
(my db version is 3.2)
List<AggregationOperation> aggregationOperations = new ArrayList<AggregationOperation>();
aggregationOperations.add(
Aggregation.project("blabla", ...).
andExpression("dateToString('%Y-%m-%d',timeCreated).as("date"));
aggregationOperations.add(Aggregation.group("date").sum("blabla").as("blabla"));
AggregationResults<?> aggregationResults = this.mongoTemplate.aggregate(
Aggregation.newAggregation(aggregationOperations),
collectionName,
resultClass);
When I use dayOfMonth(timeCreated)
to extract the day, there's no exception, but I couldn't find and example of how to make this work with dateToString
. I tried without '' for the date format, and it also didn't work...
This is the exception I get:
java.lang.NullPointerException
at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:226)
at org.bson.BasicBSONEncoder.putObject(BasicBSONEncoder.java:194)
at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:255)
at org.bson.BasicBSONEncoder.putObject(BasicBSONEncoder.java:194)
at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:255)
at org.bson.BasicBSONEncoder.putObject(BasicBSONEncoder.java:194)
at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:255)
at org.bson.BasicBSONEncoder.putIterable(BasicBSONEncoder.java:324)
at org.bson.BasicBSONEncoder._putObjectField(BasicBSONEncoder.java:263)
at org.bson.BasicBSONEncoder.putObject(BasicBSONEncoder.java:194)
at org.bson.BasicBSONEncoder.putObject(BasicBSONEncoder.java:136)
at com.mongodb.DefaultDBEncoder.writeObject(DefaultDBEncoder.java:36)
at com.mongodb.OutMessage.putObject(OutMessage.java:289)
at com.mongodb.OutMessage.writeQuery(OutMessage.java:211)
at com.mongodb.OutMessage.query(OutMessage.java:86)
at com.mongodb.DBCollectionImpl.find(DBCollectionImpl.java:81)
at com.mongodb.DB.command(DB.java:320)
at com.mongodb.DB.command(DB.java:299)
at com.mongodb.DB.command(DB.java:374)
at com.mongodb.DB.command(DB.java:246)
at org.springframework.data.mongodb.core.MongoTemplate$2.doInDB(MongoTemplate.java:357)
at org.springframework.data.mongodb.core.MongoTemplate$2.doInDB(MongoTemplate.java:355)
at org.springframework.data.mongodb.core.MongoTemplate.execute(MongoTemplate.java:442)
at org.springframework.data.mongodb.core.MongoTemplate.executeCommand(MongoTemplate.java:355)
at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1497)
at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1432)
EDIT:
Eventually we decided here on a different solution than what was suggested below, I'm writing it here in case anyone else finds it useful:
In addition to the "timeCreated" field which holds the datetime, we saved another field in the document: "date", that holds just the date (as long).
For example if "timeCreated" = "2015-12-24 16:36:06.657+02:00", then date is "2015-12-24 00:00:00", and we save 1449180000000. Now we can simply group by "date".
You could try projecting the fields first by using the SpEL andExpression
in the projection operation and then group by the new fields in the group operation:
Aggregation agg = newAggregation(
project()
.andExpression("year(timeCreated)").as("year")
.andExpression("month(timeCreated)").as("month")
.andExpression("dayOfMonth(timeCreated)").as("day"),
group(fields().and("year").and("month").and("day"))
.sum("blabla").as("blabla")
);
AggregationResults<BlaBlaModel> result =
mongoTemplate.aggregate(agg, collectionName, BlaBlaModel.class);
List<BlaBlaModel> resultList = result.getMappedResults();