I've been working with the Play! framework morphia-mongodb module and I'm impressed with its built-in group aggregation functionalities. However, all the examples I've come across demonstrate grouping/aggregating by a fixed field, whereas my requirement is to aggregate by a calculated field: timestamp grouped by day. Can anyone suggest the right approach for this?
Although resorting to a native map/reduce solution is an option (which took some effort to figure out initially, thus sharing it here for future reference using Movies and showtimes):
DBCollection coll = Movie.col();
String map = "function() { " +
(this.showtime.getMonth() + 1) + '/' + this.showtime.getDate()} "
+ "var key = {date: this.showtime.getFullYear() + '/'
+ (this.showtime.getMonth() + 1)
+ '/' + this.showtime.getDate()}; "
+ "emit(key, {count: 1}); }";
String reduce = "function(key, values) { var sum = 0; "
+ " values.forEach( function(value) {sum += value['count'];} );"
+ " return {count: sum}; }";
String output = "dailyShowingCount";
MapReduceOutput out = coll.mapReduce(
map, reduce, output, MapReduceCommand.OutputType.REPLACE, null);
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");
for (Iterator<DBObject> itr = out.results().iterator(); itr.hasNext();) {
DBObject dbo = itr.next();
String compoundKeyStr = dbo.get("_id").toString();
String compoundValStr = dbo.get("value").toString();
DBObject compKey = (DBObject)JSON.parse(compoundKeyStr);
DBObject compVal = (DBObject)JSON.parse(compoundValStr);
//don't know why count returns as a float, but it does, so i need to convert
Long dCount = new Double(
Double.parseDouble(compVal.get("count").toString())
).longValue();
Date date = df.parse(compKey.get("date").toString());
}
However, if there's an elegant built-in method within the morphia module to achieve this type of aggregation, I'd prefer that route. One idea I had was creating a virtual field in my java class (e.g. "getDay()") and then perform grouping/aggregation based on that. Has anyone tried something similar before?