MongoDB Aggregate - Query to get items that match key in first item of sort

Question

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") }

Does this answer your question? [Mongodb query specific month|year not date](https://stackoverflow.com/questions/18907566/mongodb-query-specific-monthyear-not-date) — holydragon, May 18 '22 at 07:18
We don't know the latest `$month` value until sort. After sort we don't know how to compare the top record's date in next aggregate step. — saravana priyan, May 18 '22 at 07:33

Yong Shun · Accepted Answer · 2022-05-18T09:44:23.827

$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

Thanks that worked. Had to change `key: -1` to `_id: -1` in sort. — saravana priyan, May 18 '22 at 09:32

score 2 · Answer 2 · answered May 18 '22 at 07:38

2

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

answered May 18 '22 at 07:38

sachin

1,075
1
7
11

Month 03 is dynamic. It should be calculated from latest available date. – saravana priyan May 18 '22 at 09:35

score 0 · Answer 3 · answered May 18 '22 at 08:21

0

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

answered May 18 '22 at 08:21

varman

8,704
5
19
53

Month 3 is dynamic. It should be calculated from latest available date. – saravana priyan May 18 '22 at 09:35

MongoDB Aggregate - Query to get items that match key in first item of sort

3 Answers3