I am trying to get an aggregate in ReactiveMongo 0.12 and Play Framework 2.6 (using JSON collections - not BSON) by filtering dates from a collection called "visitors". A typical document may look like this:
{ "_id": ObjectID("59c33152ca2abb344c575152"), "placeId": ObjectID("59c33152ca2abb344c575152"), "date": ISODate("2017-03-26T00:00:00Z"), "visitors": 1200 }
So from here I want to aggregate this data to get various visitor totals, averages, etc, grouping by placeId (which identifies the place in another collection) and filtering by dates after 15-05-2016.
I've based this on this similar question - without the match it works but with it - it does not. There isn't an error but it just doesn't work:
def getVisitorAggregate(col: JSONCollection) = {
import col.BatchCommands.AggregationFramework.{Group, Match, SumField, AvgField, MinField, MaxField}
val format = new java.text.SimpleDateFormat("dd-MM-YYYY")
val myDate = "15-05-2016"
val parseDate: Date = format.parse(myDate)
val longDate: Long = parseDate.getTime
col.aggregate(
Group(JsString("$placeId"))(
"totalVisitors" -> SumField("visitors"),
"avgVisitors" -> AvgField("visitors"),
"minVisitors" -> MinField("visitors"),
"maxVisitors" -> MaxField("visitors")
),
List(Match(Json.obj("date" -> Json.obj("$gte" -> JsNumber(longDate)))))
)
.map(_.head[VisitorAggregate])
}
I have looked and tested for many hours online and I cannot find the correct syntax but this will be simple for someone who knows I'm sure. Thanks
I hate to answer my own question here but now that I have figured this out I really want to clarify to others how this is done using Aggregate. Ultimately there were two parts to this question.
1) what is the syntax of querying dates?
As @AndriyKuba mentioned and I had seen in the documentation yet not fully understood; the query is formulated like this:
Json.obj("date" -> Json.obj("$gte" -> Json.obj("$date" -> JsNumber(longDate))))
2) how do I match a query within an Aggregate?
This is more of a question of the order of the query. I was originally trying to use match after grouping and aggregating the data - which is (obviously) only going to filter the data after. As I wanted to first get a date range and then aggregate that data I had to match first - this also meant that some of the syntax had to change accordingly:
def getVisitorAggregate(col: JSONCollection) = {
import col.BatchCommands.AggregationFramework.{Group, Match, SumField, AvgField, MinField, MaxField}
val format = new java.text.SimpleDateFormat("dd-MM-YYYY")
val myDate = "15-05-2016"
val parseDate: Date = format.parse(myDate)
val longDate: Long = parseDate.getTime
col.aggregate(
Match(Json.obj("date" -> Json.obj("$gte" -> Json.obj("$date" -> JsNumber(longDate))))),
List(Group(JsString("$rstId"))(
"totalVisitors" -> SumField("visitors"),
"avgVisitors" -> AvgField("visitors"),
"minVisitors" -> MinField("visitors"),
"maxVisitors" -> MaxField("visitors")
))
)
.map(_.head[VisitorAggregate])
}
Really frustrating that there isn't more documentation out there on using the Play Framework with ReactiveMongo as there are a lot of instances of trying to fathom syntax and logic.