Update the existing collection from Aggregate Pipeline

Question

I am very new to MongoDB and have been trying to create a new field within my collection that is calculated using existing data.

Is there a way to add the field myRating to the movies collection?

Here is what I came up with.


db.movies.aggregate([  
        {$unwind: "$genres"}, 
        {$project:{_id:0, title:1, genres:1, 
        durationScore: {$cond: {if: {$gte: ["$runtime", 90]}, then: 10, else: 5}},
        yearScore: {$cond: {if: {$gte: ["$year", 1990]}, then: 10, else: 5}}, 
        genreScore: {$switch:{branches:[
           {
               case: {$eq :["$genres", "Action"]}, "then": 30 ,
           },
           
           {
               case: {$eq :["$genres", "Western"]}, "then": 20 ,
           },
           
           {
               case: {$eq :["$genres", "Comedy"]}, "then": 5 ,
           },

            {
               case: {$eq :["$genres", "Drama"]}, "then": 15 ,
           },
        ],
        default: 10
        }},

            directorScore: {$switch:{branches:[
           {
               case: {$eq :["$director", "Quentin Tarantino"]}, "then": 20 ,
           },
           
           {
               case: {$eq :["$director", "Martin Scorsese"]}, "then": 20 ,
           },
        ],
        default: 10
        }}
        
        }},
        {$addFields: { myRating: { $sum: [ "$yearScore", "$durationScore", "$genreScore", "$directorScore" ]}}},

])

Sample of Data.

{
  "_id": {
    "$oid": "60502686eb0d3e3e849677ef"
  },
  "title": "Once Upon a Time in the West",
  "year": 1968,
  "rated": "PG-13",
  "runtime": 175,
  "countries": [
    "Italy",
    "USA",
    "Spain"
  ],
  "genres": [
    "Western"
  ],
  "director": "Sergio Leone",
  "writers": [
    "Sergio Donati",
    "Sergio Leone",
    "Dario Argento",
    "Bernardo Bertolucci",
    "Sergio Leone"
  ],
  "actors": [
    "Claudia Cardinale",
    "Henry Fonda",
    "Jason Robards",
    "Charles Bronson"
  ],
  "plot": "Epic story of a mysterious stranger with a harmonica who joins forces with a notorious desperado to protect a beautiful widow from a ruthless assassin working for the railroad.",
  "poster": "http://ia.media-imdb.com/images/M/MV5BMTEyODQzNDkzNjVeQTJeQWpwZ15BbWU4MDgyODk1NDEx._V1_SX300.jpg",
  "imdb": {
    "id": "tt0064116",
    "rating": 8.6,
    "votes": 201283
  },
  "tomato": {
    "meter": 98,
    "image": "certified",
    "rating": 9,
    "reviews": 54,
    "fresh": 53,
    "consensus": "A landmark Sergio Leone spaghetti western masterpiece featuring a classic Morricone score.",
    "userMeter": 95,
    "userRating": 4.3,
    "userReviews": 64006
  },
  "metacritic": 80,
  "awards": {
    "wins": 4,
    "nominations": 5,
    "text": "4 wins & 5 nominations."
  },
  "type": "movie"
}

user_14732669 · Answer

I would suggest you keep _id field in $project stage.

Without considering performance, simply iterating through the aggregate result and $set myRating field through updateOne using the _id field.

db.movies.aggregate([
    ...
    {$project:{_id:1, title:1, genres:1,
    ...
]).forEach(result = > {
    db.movies.updateOne(
        {_id : result._id},
        {$set : {myRating : {result.myRating}}
    })
})

user_5070460 · Answer

Starting in MongoDB 4.2, you can use the aggregation pipeline for update operations. Try this query:

db.movies.updateOne(
  { "_id": ObjectId("60502686eb0d3e3e849677ef") },
  [
    {
      $set: {
        myRating: {
          $let: {
            vars: {
              durationScore: { $cond: { if: { $gte: ["$runtime", 90] }, then: 10, else: 5 } },
              yearScore: { $cond: { if: { $gte: ["$year", 1990] }, then: 10, else: 5 } },
              genreScore: {
                $switch: {
                  branches: [
                    { case: { $in: ["Action", "$genres"] }, "then": 30 },
                    { case: { $in: ["Western", "$genres"] }, "then": 20 },
                    { case: { $in: ["Comedy", "$genres"] }, "then": 5 },
                    { case: { $in: ["Drama", "$genres"] }, "then": 15 }
                  ],
                  default: 10
                }
              },
              directorScore: {
                $switch: {
                  branches: [
                    { case: { $eq: ["$director", "Quentin Tarantino"] }, "then": 20 },
                    { case: { $eq: ["$director", "Martin Scorsese"] }, "then": 20 }
                  ],
                  default: 10
                }
              }
            },
            in: { $sum: ["$$yearScore", "$$durationScore", "$$genreScore", "$$directorScore"] }
          }
        }
      }
    }
  ]
);

Update the existing collection from Aggregate Pipeline

2 Answers