1

Below is a part of my json structure :

   {
       "dateOfCalculation" : ISODate("2018-08-13T06:02:48.587Z"),
       "value" : 6.0
   },
   {
       "dateOfCalculation" : ISODate("2018-08-13T06:04:38.294Z"),
       "value" : 8.0
   },
   {
       "dateOfCalculation" : ISODate("2018-08-30T07:21:08.444Z"),
       "value" : 5.0
   },    
   {
       "dateOfCalculation" : ISODate("2018-10-01T10:04:33.564Z"),
       "value" : 5.58333349227905
   },
   {
       "dateOfCalculation" : ISODate("2018-10-24T11:41:24.188Z"),
       "value" : 5.16666650772095
   },
   {
       "dateOfCalculation" : ISODate("2018-10-26T14:03:54.672Z"),
       "value" : 5.58333349227905
   },    
   {
       "dateOfCalculation" : ISODate("2019-01-10T15:05:44.842Z"),
       "value" : 3.5
   },
   {
       "dateOfCalculation" : ISODate("2019-01-21T10:08:52.429Z"),
       "value" : 6.0
   },
   {
       "dateOfCalculation" : ISODate("2019-01-21T10:38:57.468Z"),
       "value" : 5.16666650772095
   },
   {
       "dateOfCalculation" : ISODate("2019-01-25T14:01:56.779Z"),
       "value" : 6.0
   }

Using Spring-data-mongodb, I would like to group these on the basis of month and fetch the value recorded on the latest date. Something like below : For the 8th month - 

    {
        "dateOfCalculation" : ISODate("2018-08-30T07:21:08.444Z"),
        "value" : 5.0
    },

For the 1st month - 

   {
       "dateOfCalculation" : ISODate("2019-01-25T14:01:56.779Z"),
       "value" : 6.0
   }

What kind of aggregation operations should I use in Spring-data-mongodb so that I can group them based on month first and then fetch the latest of that month?

SnS
  • 175
  • 2
  • 16

1 Answers1

0

The MongoDB query would look like this: 

db.yourCollection.aggregate([
    {$addFields: {month: {$month :"$dateOfCalculation"}}},
    {$group:{_id: {month:"$month"},documents:{$push:"$$ROOT"}}},
    {$project:{lastestDate: {$arrayElemAt:["$documents", {$indexOfArray:["$documents",{$max:"$documents.dateOfCalculation"}]}]}}}
])

This aggregation will return document with the following schema:

{
    "_id" : {
        "month" : 1
    },
    "lastestDate" : {
        "_id" : ObjectId("5c59c05fb6f5b50bd40235a1"),
        "dateOfCalculation" : ISODate("2019-01-25T15:01:56.779+01:00"),
        "value" : 6,
        "month" : 1
    }
}

And please find bellow an example to make it using Spring data MongoDB.

ProjectionOperation projectionOperation1 = project("dateOfCalculation","value").andExpression("month(dateOfCalculation)").as("month");
GroupOperation groupOperation = group("month").push("$$ROOT").as("documents");
ProjectionOperation projectionOperation2 = project().and(ArrayOperators.ArrayElemAt.arrayOf("documents")
        .elementAt(ArrayOperators.IndexOfArray.arrayOf("documents")
                .indexOf(AccumulatorOperators.Max.maxOf("documents.dateOfAccumulation")))).as("latestDate");
 List<yourEntityObject> result = mongoTemplate.aggregate(Aggregation.newAggregation(projectionOperation1,groupOperation,projectionOperation2)
        , "yourCollection", yourEntityObject.class).getMappedResults();

MongDB $addFileds operator is not implemented in Spring MongoDB so you have to do the same using a ProjectionOperation. Regarding to the $month operator you can eithor use SpEL andExpression() or DateOperators.Month.

charlycou
  • 1,778
  • 13
  • 37