mongoDB aggregate method find movies from last 20 years

I am trying to get all the movies from the latest 20 years of the document. I am trying like this but doesn’t work. It´s the first time I use mongoDB so I am a bit lost, it may be wrong from the beginning.

var query1 = { "_id": null, "maxYear": { $max: "$year" } }
var fase1 = { $group: query1 }

var query2 = { "_id": null, "minYear": { $subtract: [ "$maxYear" , 20 ] } }
var fase2 = { $project: query2 }

var query3 = { "year": { $gte: "$minYear" } }
var fase3 = { $match: query3 }

var etapas = [ fase1, fase2, fase3 ]
db.movies.aggregate( etapas )

here is an example of my data:

/* 1 createdAt:4/12/2023 23:56:33*/
{
    "_id" : ObjectId("656e59214247fdc6cb90136d"),
    "title" : "Caught",
    "year" : 1900,
    "cast" : [ "Undefined" ],
    "genres" : [ "Undefined" ]
},

Expecting the movies from the last 20 years of the doc.

I recommand you to test and see for yourself what is happening with your queries when you remove some stages of your aggregation.

After your first step called fase1, all your documents will be grouped in a single document with null _id. (So you have no more the informations about the docs since you didn’t keep them in your group stage)

See on mongoplayground.

After your fase2, the field minYear will be succesfully added to your single document with null _id. But you’ll loose the maxYear field since you didn’t include it in your $project (this is fine)

See on mongoplayground.

Then in fase3, you try to match docs where year field is greater than min. But at this point, you only have one document on which there is no year field. No documents will ever match.

See on mongoplayground.

This being said, there is plenty option for you to do the query you wanted.

Considering you try to query all the documents where the year is more than the maxYear – 20.

You could keep the documents in your $group stage, or even a self $lookup stage to re query the whole collection.

Here is a way to do it with $lookup on mongoplayground.

If I understand your question correctly, you are trying to get the document with year from the year 2003 to 2023, the latest 20 years to now.

In the filter query, you should get the current year, and subtract with the year field. Filter the document with the result which is less or equal to 20.

db.movies.aggregate([
  {
    $match: {
      $expr: {
        $lte: [
          {
            $subtract: [
              {
                $year: "$$NOW"
              },
              "$year"
            ]
          },
          20
        ]
      }
    }
  }
])

Demo @ Mongo Playground

If you want to get the max value of year from the collection and filter the documents with max year - year is 20 or below, you are needed for the $setWindowFields stage.

db.movies.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "year": -1
      },
      "output": {
        "maxYear": {
          $first: "$year",
          "window": {
            "documents": [
              "unbounded",
              "current"
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $lte: [
          {
            $subtract: [
              "$maxYear",
              "$year"
            ]
          },
          20
        ]
      }
    }
  },
  {
    $unset: "maxYear"
  }
])

Demo @ Mongo Playground