MongoDB - query to get items that match key in first item of sort in aggregate-CodePudding

We have the following records in a collection:

{ "_id" : ObjectId("1"), "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("2"), "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("3"), "date" : ISODate("2017-03-03T00:00:00Z") }
{ "_id" : ObjectId("4"), "date" : ISODate("2017-02-02T00:00:00Z") }
{ "_id" : ObjectId("5"), "date" : ISODate("2017-03-01T00:00:00Z") }
{ "_id" : ObjectId("6"), "date" : ISODate("2017-02-01T00:00:00Z") }
{ "_id" : ObjectId("7"), "date" : ISODate("2017-01-02T00:00:00Z") }
{ "_id" : ObjectId("8"), "date" : ISODate("2017-01-03T00:00:00Z") }

How to filter the most recent records by $month of date field like this:

{ "_id" : ObjectId("3"), "date" : ISODate("2017-03-03T00:00:00Z") }
{ "_id" : ObjectId("5"), "date" : ISODate("2017-03-01T00:00:00Z") }

CodePudding user response：

$group - Group by the first day of the month year from date and add documents into data array.
$sort - Sort by _id DESC.
$skip
$limit - Take the first document from the result.
$unwind - Deconstruct the data array to multiple documents.
$replaceWith - Replace the document with data document.

db.collection.aggregate([
  {
    $group: {
      _id: {
        "$dateFromParts": {
          "year": {
            $year: "$date"
          },
          "month": {
            $month: "$date"
          },
          "day": 1
        }
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $sort: {
      _id: -1
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 1
  },
  {
    $unwind: "$data"
  },
  {
    $replaceWith: "$data"
  }
])

Sample Mongo Playground

CodePudding user response：

If you're using collection.find() method, use sort method along with that.
like this : collection.find().sort({created : -1})

If you want to filter, the look at this once :

collection.find({ $expr: {
$eq: [{ $month: "$date" }, 03]
}});

Hope this helps!.

CodePudding user response：

If you are willing to use aggregations, you can use

$month to get the month from date
$match to filter the value
$sort to sort by ASC or DESC
$project to keep or remove fields

here is the code

db.collection.aggregate([
  {
    "$addFields": {
      month: { $month": "$date"}
    }
  },
  { "$match": { month: 3 } },
  { "$sort": { date: 1}},
  { "$project": { date: 1 } }
])

Working Mongo playground