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

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 doesnt work. It´s the first time I use mongoDB so I am a bit lost, it may be wrong from the begining.

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

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

Back To Top
Search