dateplayframeworktype-conversionreactivemongoreactivemongo-play-json

Aggregate data in ReactiveMongo matching by date


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


Solution

  • 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.