I have this problem. Consider that we have many documents with movies and year of release. Here is an example of the documents:

{
    "_id" : ObjectId("63a994974ac549c5ea982d2b"),
    "title" : "Destroyer",
    "year" : 2018
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d2a"),
    "title" : "Aquaman",
    "year" : 2014
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d29"),
    "title" : "On the Basis of Sex",
    "year" : 1998   
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d28"),
    "title" : "Holmes and Watson",
    "year" : 1940
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d27"),
    "title" : "Conundrum: Secrets Among Friends",
    "year" : 1957
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d26"),
    "title" : "Welcome to Marwen",
    "year" : 2000
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d25"),
    "title" : "Mary Poppins Returns",
    "year" : 1997
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d24"),
    "title" : "Bumblebee",
    "year" : 2018
},

Therefore, I want to count movies that are between the maximum year that has been registered in the documents and 20 years before, i.e., 2018 and 1998.

What I tried is as follows:

var query1 = {"$addFields": {maxium: {$max: "$year"}, minimum : {$subtract: [{$max: "$year"}, 20]}}}

var filter = {"year": {"$lte": maximum, "$gte": minimum}}

var logic = {$match: {$and: [filter]}}

var query1 = {$group: {"_id": null, "count": {$sum:1}}}

var stage = [logic, query1]

db.movies.aggregate(stage)

But I can't get the right output. What I have got is the following output:

{
    "message" : "maximum is not defined",
    "stack" : "script:3:32"
}

I do not know what I am doing wrong. For the example before, this it'd be the right output:

     "_id": null,
     "count": 4

How can I solve this? How can I count all movies between years dinamically, i.e., with $max and $subtract..?

Best regards! Thanks!!!

CodePudding user response：

In order to get the max year you need to group the documents or to use $setWindowFields to compare them. One option is to use $setWindowFields which allows you to avoid grouping all documents into one large document as document has a size limit:

db.collection.aggregate([
  {$setWindowFields: {
      sortBy: {year: -1},
      output: {
        maxYear: {
          $max: "$year",
          window: {documents: ["unbounded", "current"]}
        }
      }
  }},
  {$match: {$expr: {$lt: [{$subtract: ["$maxYear", "$year"]}, 20]}}},
  {$group: {_id: 0, count: {$sum: 1}}}
])

See how it works on the playground example