Aggregate data in ReactiveMongo matching by date

Question

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

score 1 · Answer 1 · answered Sep 30 '17 at 10:43

ISODate is a mongodb type, and Model.aggregate does not cast the arguments, so "date" -> Json.obj("$gte" -> JsNumber(longDate)) is wrong.

You need to use a type that will be converted to the ISODate, I am pretty sure it is not JsNumber.

It is a BSONDateTime type would you use BSON, but you do not.

According to documentation it must be a

JsObject with a $date JsNumber field with the timestamp (milliseconds) as value

So solution can be (I did not verify):

Match(Json.obj("date" -> Json.obj("$gte" -> Json.obj("$date" -> JsNumber(longDate)))))

I saw that documentation and already tried this but it isn't the solution Andriy. Just made sure now and it doesn't work — jesus g_force Harris, Sep 30 '17 at 16:30

score 0 · Accepted Answer · answered Sep 30 '17 at 20:27

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.

As I remember/can see, on one side every element of the solution is mentioned on the documentation, on the other side the documentation is open. — cchantep, Oct 03 '17 at 07:08

Aggregate data in ReactiveMongo matching by date

2 Answers2