Use $Match to filter MongoDB data between the last N years
P粉211273535
P粉211273535 2024-01-29 15:18:25
0
1
542

I am new to MongoDB. I want to use match to get only records between the last 2 years. Is there a way to do this without hard coding?

For example, the year is 2023. But the latest year recorded in my collection is 2021. I want to get My Collection's records for the last 2 years, not the current year. So the range will be 2019-2021, instead of 2021-2023.

Is there a way to do this without hardcoding the year as a string?

I want to get this information so I can use it in the next stage

Currently I have this, hardcoded

{
        $match: {
          fechaOrden: {
            $gte: ISODate("2018-01-01"),
            $lt: ISODate("2020-02-01"),
          }, 
        },
      },
    ] ```

P粉211273535
P粉211273535

reply all(1)
P粉146080556

You can use $setWindowFields to capture the records of the document in the past 2 years. Then, use $sort $limit to find the most recent record in the entire collection and return the associated records from the last 2 years.

db.collection.aggregate([
  // compute latest records in 2 years
  {
    $setWindowFields: {
      sortBy: {
        dt: 1
      },
      output: {
        recentRecords: {
          $push: "$$ROOT",
          window: {
            range: [
              -2,
              0
            ],
            unit: "year"
          }
        }
      }
    }
  },
  // find latest records in whole collection
  {
    "$sort": {
      dt: -1
    }
  },
  {
    $limit: 1
  },
  // cosmetics
  {
    "$unwind": "$recentRecords"
  },
  {
    "$replaceRoot": {
      "newRoot": "$recentRecords"
    }
  }
])

Mongo Playground

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template