I have the following sample collection of movies:

[
    {
       "title":"Boots and Saddles",
       "year":1909,
       "cast":[],
       "genres":[]
    },
    {
       "title":"The Wooden Leg",
       "year":1909,
       "cast":[],
       "genres":[]
    },
    {
       "title":"The Sanitarium",
       "year":1910,
       "cast":["Fatty Arbuckle"],
       "genres":["Comedy"]
    },
    {
       "title":"Snow White",
       "year":1916,
       "cast":["Marguerite Clark"],
       "genres":["Fantasy"]
    },
    {
       "title":"Haunted Spooks",
       "year":1920,
       "cast":["Harold Lloyd"],
       "genres":["Comedy"]
    },
    {
       "title":"Very Truly Yours",
       "year":1922,
       "cast":["Shirley Mason", "lan Forrest"],
       "genres":["Romance"]
    }
 ]

I want to count number of movies appeared in the last 20 years (from the last movie recorded in this collection).

I have following query to find which year is the most recent movie (result shows 2018):

      db.movies.find({},{"_id":0, "year":1}).sort({year:-1}).limit(1) 

So to find how many movies appeared in the last 20 years I wrote this:

      db.movies.aggregate([{$match:{year:{$gte:1999}}},{$count:"title"}])

However, this is not very optimized, because if the database is modified or updated,I will have to modify that query every time.

Is there a more elegant way to find the result?

Thank you in advance!

CodePudding user response：

You can use mongodb aggregate method.

db.movies.aggregate([
    { $sort: { year: -1 } },
    { $limit: 1 },
    { 
        $project: {
            currentYear: { $year: new Date() },
            latestMovieYear: "$year",
            last20Years: { $subtract: [ "$currentYear", 20 ] }
        }
    },
    {
        $match: {
            year: { $gte: "$last20Years", $lte: "$latestMovieYear" }
        }
    },
    { $count: "movies" }
]);

• Sort the documents by year in descending order, and limit the number of documents to 1. It will return latest movie present in the collection.

• Use the $project operator to create a new field currentYear that returns the current year, latestMovieYear that returns the year of the latest movie, and last20Years that subtracts 20 from the current year.

• Use $match operator to filter out the movies that have a year greater than or equal to last20Years and less than or equal to latestMovieYear.

• Use the $count operator to count the number of documents that match the above criteria.