Get MongoDB records of the last day in the previous month-CodePudding

I've a MongoDB collection that contains records with a field called createdAt, I want to get the last createdAt in the previous month of a given date using mongoTemplate aggregation in a spring boot application.

example of a record:

{
  createdAt: new Date('2022-11-01'), 
  //other fields ...
}

Note:

Let say for 22/11/2022 given date, and we have records with createdAt fields are 12/10/2022, 22/10/2022, 27/10/2022; I wanna get the date : 27/10/2022

CodePudding user response：

if you are using mongo template you could find it like this:

template.aggregate(
                        newAggregation(
                            match(Criteria.where("createdAt").lt(new Date(2022,11,01))),//<--- here you pass the month in question
                            project("createdAt").andExclude("_id"),
                               group().addToSet("createdAt").as("LATEST_DATES"),
                                unwind("LATEST_DATES"),
                                sort(Sort.Direction.DESC, "LATEST_DATES"),
                                limit(1),//to get only one
                                project().andExclude("_id")
                        ),
                        "YOUR COLLECTION NAME",
                        DBObject.class
                )

CodePudding user response：

You can do that like this : First create a date of first day of current month.

firstDayOfCurrentMonth = LocalDate.now().withDayOfMonth(1))

then use it in query:

newAggregation(
      match(Criteria.where("createdAt").lt(firstDayOfCurrentMonth))),
      sort(Sort.Direction.DESC, "createdAt"),
      limit(1)
    )