MongoDB- Subtract

Question

Basically I want to subtract finish-start for each object.
So in the below example, the output should be 2 and 4 ((7-5=2) and (8-4=4)) and then I want to add that both fields into my existing documents.

How can I do this in MongoDB?

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

barrypicker barrypicker · Accepted Answer · 2019-12-05T22:06:16

Not sure what exactly are the requirements for query output but this might help..

Query:

db.books.aggregate([
    { $unwind: "$ratings" },
    { $addFields: { "ratings.diff": { $subtract: [ "$ratings.finish", "$ratings.start" ] } } }
])

Pipeline Explanation:

Unwind the array because the $subtract cannot act on array data.
Add a new field called diff in the 'ratings' sub documents that has the calculated value

EDIT:

OP asks for the results to be stored in the same subdocument. After discussions with friends and family I discovered this is possible but only with MongoDB version 4.2 or later. Here is an update statement that can achieve the desired results. This is an update not an aggregation.

Update: (MongoDB 4.2 or later specific)

db.books.update({
},
[
  {
    $replaceWith: {
      ratings: {
        $map: {
          input: "$ratings",
          in: {
            start: "$$this.start",
            finish: "$$this.finish",
            diff: {
              $subtract: [
                "$$this.finish",
                "$$this.start"
              ]
            }
          }
        }
      }
    }
  }
])

MongoDB- Subtract

1 Answers