MongoDB - Update the array's sub-document to include the calculated value

Question

Basically, I want to subtract two fields from each sub-object in an array field of an input document and then update the document to include the calculated value.

For example,

{   
    "title" : "The Hobbit",

    "rating_average" : "???",
    "ratings" : [
        {
            "title" : "best book ever",
            "start" : 5,
            "finish" : 7
        },
        {
            "title" : "good book",
            "start" : 4,
            "finish" : 8
        }
    ]
}

I want to subtract finish-start for each object and then save that new calculated field into my existing documents.

So after updating my documents it should look like:

{   
    "title" : "The Hobbit",
    "rating_average" : "???",
    "ratings" : [
        {
            "title" : "best book ever",
            "start" : 5,
            "finish" : 7
             “diff”:2
        },
        {
            "title" : "good book",
            "start" : 4,
            "finish" : 8
             “diff”:4

        }
    ]
}

So, how can I do this?

Thanks in advance.

Answer 1

Here is the the version 4.2 update method which supports aggregation pipeline stage $set. The $map projection operator is used to derive the diff field for each of the array elements:

db.test.updateOne(
   {  },
   [
       {
         $set: {
             ratings: {
                 $map: {
                     input: "$ratings",
                        as: "rating",
                        in: {
                             $mergeObjects: [
                                 "$$rating",
                                 { "diff" : { $subtract: [ "$$rating.finish", "$$rating.start" ] } }
                             ]
                        }
                  }
              }
          }
      }
  ]
)

Answer 2

the following aggregation pipeline will create a new collection with diff field in the array.

db.books.aggregate([
    {
        $unwind: "$ratings"
    },
    {
        $addFields: {
            "ratings.diff": {
                $subtract: ["$ratings.finish", "$ratings.start"]
            }
        }
    },
    {
        $group: {
            _id: "$_id",
            title: {
                $first: "$title"
            },
            rating_average: {
                $first: "$rating_average"
            },
            ratings: {
                $addToSet: "$ratings"
            }
        }
    },
    {
        $out: "modified-books"
    }
])

you can even replace the existing colleciton by specifying $out: "books" in the last stage.

this is not an ideal way to get this done but i don't think there's any other alternatives.