I have 3 collections.

The user_movie collection keeps the relationship of the user and the movies he added to his list. The membership_date field is when the user is subscribed.

• The user wants to see the reviews of the movies he added to his list.

• But when showing these reviews, I want to show the comments after the subscription date.

With the query I tried, the user sees all the reviews.

Collection structure

db={
  "user_movie": [
   {
    "_id" : 1,
    "movie_id" : 1,
    "user_id" : 1,
    "status" : true,
    "membership_date" : ISODate("2021-01-01")
    },
    {
    "_id" : 2,
    "movie_id" : 2,
    "user_id" : 1,
    "status" : true,
    "membership_date" :  ISODate("2021-01-01")
    },
    {
    "_id" : 3,
    "movie_id" : 3,
    "user_id" : 1,
    "status" : true,
    "membership_date" :  ISODate("2022-01-02")
    }
  ],
  "movie": [
    {
        "_id" : 1,
        "movie_name" : "fugiat nulla",
    },
    {
        "_id" : 2,
        "movie_name" : "sint occaecat",
    },
    {
        "_id" : 3,
        "movie_name" : "cupidatat non",
    }
  ],
  "movie_reviews": [
   {
        "_id" : 1,
        "movie_id" : 1,
        "review": "Lorem ipsum dolor"
        "review_date" : ISODate("2021-01-02"),
    },
    {
        "_id" : 2,
        "movie_id" : 2,
        "review": "Consectetur adipiscing elit"
        "review_date" : ISODate("2021-01-02"),
    },
    {
        "_id" : 3,
        "movie_id" : 3,
        "review": "Do eiusmod tempor"
        "review_date" : ISODate("2021-01-02"),
    },
    {
        "_id" : 4,
        "movie_id" : 3,
        "review": "Abore et dolore magna"
        "review_date" : ISODate("2022-01-01"),
    }
  ]
}

The query I tried gives this output.

[
{
        "_id" : 1,
        "movie_id" : 1,
        "date" : ISODate("2021-01-02"),
    },
    {
        "_id" : 2,
        "movie_id" : 2,
        "date" : ISODate("2021-01-02"),
    },
    {
        "_id" : 3,
        "movie_id" : 3,
        "date" : ISODate("2021-01-02"),
    },
    {
        "_id" : 4,
        "movie_id" : 3,
        "date" : ISODate("2022-01-01"),
    }
]

But this is not the output I was expecting. The movie reviews with id 3,4 should not be seen by the user. Because it was written before the membership_date date, so the user should not see these reviews.

How can I get all movie reviews of the user with id 1 as mentioned?

CodePudding user response：

Stage 1: $lookup with pipeline

• Join conditions:

1. By movie_id.
2. movie_review's review_date must not be earlier ($gte) than membership_date (from user_movie).

Stage 2: $unwind

• Deconstruct movie_reviews array field to multiple document.

Stage 3: $replaceWith

• Decorate the output document to show movie_review document.

db.user_movie.aggregate([
  {
    "$lookup": {
      "from": "movie_reviews",
      let: {
        movie_id: "$movie_id",
        membership_date: "$membership_date"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$movie_id",
                    "$$movie_id"
                  ]
                },
                {
                  $gte: [
                    "$review_date",
                    "$$membership_date"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "movie_reviews"
    }
  },
  {
    $unwind: "$movie_reviews"
  },
  {
    "$replaceWith": "$movie_reviews"
  }
])

Sample Mongo Playground

Updated: Lookup join movie_reviews with user_movie

Since you are just to get movie_reviews, I think it is better to join from movie_reviews with user_movie instead of joining from user_movie with movie_reviews to get rid of the use of $unwind.

db.movie_reviews.aggregate([
  {
    "$lookup": {
      "from": "user_movie",
      let: {
        movie_id: "$movie_id",
        review_date: "$review_date"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$movie_id",
                    "$$movie_id"
                  ]
                },
                {
                  $gte: [
                    "$$review_date",
                    "$membership_date"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "user_movie"
    }
  },
  {
    $match: {
      "user_movie": {
        $ne: []
      }
    }
  },
  {
    $project: {
      user_movie: 0
    }
  }
])

Sample Mongo Playground (Get movie_reviews)