Question 117931 in Mongodb Question posted in
The official documentation can be found here.

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

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

3

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    1. $group – Group by the first day of the month year from date and add documents into data array.

    2. $sort – Sort by _id DESC.

    3. $skip

    4. $limit – Take the first document from the result.

    5. $unwind – Deconstruct the data array to multiple documents.

    6. $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

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search