Group by time interval spring-data-mongo

Question

Below is my document structure:

{
   "_id":"5c59c35d8610f702d00e6f70",
   "stationId":"2",
   "listenerId":"807",
   "streamId":"37",
   "userAgentId":"7",
   "botDefinitionId":"18",
   "ipAddress":"50.116.14.48",
   "startTime":"2018-02-06T12:51:59.000Z",
   "endTime":"2018-02-06T12:53:56.000Z",
   "listenLength":"117",
   "totalDataUsed":"1433582",
}

Using spring data mongo I would like to group these into windows of time (lets say 15 min intervals). I have created the following working query:

{
   '_id':{
      'year':{
         '$year':'$startTime'
      },
      'week':{
         '$week':'$startTime'
      },
      'dayOfMonth':{
         '$dayOfMonth':'$startTime'
      },
      'month':{
         '$month':'$startTime'
      },
      'hour':{
         '$hour':'$startTime'
      },
      'interval':{
         '$subtract':[
            {
               '$minute':'$startTime'
            },
            {
               '$mod':[
                  {
                     '$minute':'$startTime'
                  },
                  15
               ]
            }
         ]
      }
   },
   'count':{
      '$sum':1
   }
}

Which then returns me the following document:

_id:{
   year:2018   week:15   dayOfMonth:18   month:4   hour:18   interval:45
},
count:9

How do I use the GroupOperation to specify form this aggregation in spring-data-mongo?

score 1 · Accepted Answer · answered Feb 13 '19 at 10:31

One way I can think of is to call functions in $project stage instead of calling them in $group stage.

Extract out fields like hour, minute, year etc using DateOperator class. Then use SpEL andExpression for your interval field as:

andExpression("minute - minute % 15").as("interval")

This will reshape your document with fields as hour, interval, year etc. Then group them on interval and other fields you want.

Aggregation agg = Aggregation.newAggregation(

    Aggregation.project()
        .and(DateOperators.Minute.minute("$timestamp")).as("minute")
        .and(DateOperators.Hour.hour("$timestamp")).as("hour")
        .and(DateOperators.DayOfMonth.dayOfMonth("$timestamp")).as("dayOfMonth"),

    Aggregation.project("hour","dayOfMonth","minute")
        .andExpression("minute - minute % 15").as("interval"),

    Aggregation.group("hour","dayOfMonth","interval")
        .addToSet("hour").as("hour")
        .addToSet("dayOfMonth").as("dayOfMonth")
        .addToSet("interval").as("interval")
        .count().as("count")
);

List<QueryResult> result = mongoTemplate.aggregate(agg, "collection_name", QueryResult.class).getMappedResults();

Group by time interval spring-data-mongo

1 Answers1