Mongoose - How to incrementally subtract/reduce the value in subdocument array which is in another subdocument array

Question

This one is pretty tricky,

I have this sample document

[{
  _id: '111',
  products: [{
    _id: 'productId',
    items: [{
      _id: 'aaa',
      quantity: 5
    }, {
      _id:'bbb',
      quantity: 8
    }]
  }]
}]

Now when I buy an Item, I need the quantity to be reduced starting from the first one in items array down to the second, third etc...

This is to say, when I buy an Item, I need the quantity to be reduced in the first element of items array, and If the quantity to be reduced exceed, it should reduce the remained quantity in the second items array...and so forth

For example, If I buy 3 items, It should minus 3 items in the first array element and the resulting items array should be

...
    items: [{
      _id: 'aaa',
      quantity: 2
    }, {
      _id: 'bbb',
      quantity: 8
    }]

On the other hand, If I would have bought 7 items, it should reduce all 5 items from first array element and then remove 2 items from the second element...and hence the final array would be like

...
    items: [{
      _id: 'aaa',
      quantity: 0
    }, {
      _id: 'bbb',
      quantity: 6
    }]

WHAT I HAVE TRIED:

I tried using findOneAndUpdate like this, just to reach the items array, but I have no Idea how i should continue with the items part.

        Shop.findOneAndUpdate(
            { _id: '111', products._id: 'productId' }
        )

Answer 1

Although this is possible with MongoDB v4.2 (update pipeline) where you can update documents based on their current value. It would be very complex as you don't really have a native way to keep local variables (to keep track of how many items you already subtracted). You could use $reduce to keep that value, but it's going to look very complex as you have deeply nested data structures. It will be much easier if you can do it in the application layer, but then you have concurrency issues if your document changes in between.

Suggestions

1. Change your data structure, to be flatter, possibly separate into multiple collections. It's difficult to do updates in deeply nested items
2. Wait for MongoDB v4.4, where you have $accumulator and $function operators that can keep state and is more flexible to process your data using JavaScript
3. Use MongoDB v4.2 update pipeline, along with $reduce and $cond. With this approach, you have to maintain the state using $reduce and apply the quantity value according to the state, since you have nested array structures, you'll have to use $map as well.
4. As mentioned above, you can implement query the document, update in your application and put the change back to MongoDB, but you have to make sure there is no concurrent operation that is trying to update your document between read and write
5. Update: another solution is to wait for v4.4 $merge aggregation stage, which lets you output aggregation pipeline result to the same collection.

Update: I gave a try to implement #3 to show the complexity. Inspirations taken from an answer on your other question

Shop.updateOne({
  "_id": '111', "products._id": "productId" // don't forget to replace "productId"
}, [{
    "$set":{
      "products":{
        "$map":{
          "input":"$products", // iterate through products, we can not update inside the array directly as "arrayFilters" update option isn't available in pipeline update
          "in":{
            "$cond":[
              {
                "$ne":[
                  "$$this._id",
                  "productId" // don't forget to replace "productId"
                ]
              },
              "$$this",
              {
                "$mergeObjects":[
                  "$$this",
                  {
                    "items":{
                      "$reduce":{
                        "input":"$$this.items",
                        "initialValue":{ // initialise accumulator
                          "acc": 7, // the number you want to subtract
                          "items":[]
                        },
                        "in":{
                          "acc":{
                            "$subtract":[
                              "$$value.acc",
                              {
                                "$min":[
                                  "$$this.quantity",
                                  "$$value.acc"
                                ]
                              }
                            ]
                          },
                          "items":{
                            "$concatArrays":[
                              "$$value.items",
                              [
                                {
                                  "$mergeObjects":[
                                    "$$this",
                                    {
                                      "quantity":{
                                        "$subtract":[
                                          "$$this.quantity",
                                          {
                                            "$min":[
                                              "$$this.quantity",
                                              "$$value.acc"
                                            ]
                                          }
                                        ]
                                      }
                                    }
                                  ]
                                }
                              ]
                            ]
                          }
                        }
                      }
                    }
                  }
                ]
              }
            ]
          }
        }
      }
    }
  }, // at this stage, you'll have all information you need, add the next stage only if you want it in the same structure
  {
    "$set":{
      "products":{
        "$map":{
          "input":"$products",
          "in":{
            "$cond":[
              {
                "$ne":[
                  "$$this._id",
                  "productId"
                ]
              },
              "$$this",
              {
                "$mergeObjects":[
                  "$$this",
                  {
                    "items":{
                      "$ifNull":[
                        "$$this.items.items",
                        "$$this.items"
                      ]
                    }
                  }
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Update #2 A "shorter" version which requires only one stage, using array instead of object to keep the accumulator.

Shop.updateOne({
  "_id": '111', "products._id": "productId" // don't forget to replace "productId"
}, [
  {
    "$set": {
      "products": {
        "$map": {
          "input": "$products",
          "in": {
            "$cond": [
              {
                "$ne": [
                  "$$this._id",
                  "productId" // don't forget to replace "productId"
                ]
              },
              "$$this",
              {
                "$mergeObjects": [
                  "$$this",
                  {
                    "items": {
                      "$arrayElemAt": [
                        {
                          "$reduce": {
                            "input": "$$this.items",
                            "initialValue": [
                              7, // the number you want to subtract
                              []
                            ],
                            "in": [
                              {
                                "$subtract": [
                                  {
                                    "$arrayElemAt": [
                                      "$$value",
                                      0
                                    ]
                                  },
                                  {
                                    "$min": [
                                      "$$this.quantity",
                                      {
                                        "$arrayElemAt": [
                                          "$$value",
                                          0
                                        ]
                                      }
                                    ]
                                  }
                                ]
                              },
                              {
                                "$concatArrays": [
                                  {
                                    "$arrayElemAt": [
                                      "$$value",
                                      1
                                    ]
                                  },
                                  [
                                    {
                                      "$mergeObjects": [
                                        "$$this",
                                        {
                                          "quantity": {
                                            "$subtract": [
                                              "$$this.quantity",
                                              {
                                                "$min": [
                                                  "$$this.quantity",
                                                  {
                                                    "$arrayElemAt": [
                                                      "$$value",
                                                      0
                                                    ]
                                                  }
                                                ]
                                              }
                                            ]
                                          }
                                        }
                                      ]
                                    }
                                  ]
                                ]
                              }
                            ]
                          }
                        },
                        1
                      ]
                    }
                  }
                ]
              }
            ]
          }
        }
      }
    }
  }
])