How to bulkWrite decrease quantity in products when order mongodb?

Question

I have table "products" in mongodb example:

{
"_id": "62ab02ebd3e608133c947798",
"status": true,
"name": "Meat",
"type": "62918ab4cab3b0249cbd2de3",
"price": 34400,
"inventory": [
  {
    "_id": "62af007abb78a63a44e88561",
    "locator": "62933b3fe744ac34445c4fc0",
    "imports": [
      {
        "quantity": 150,
        "_id": "62aefddcd5b52c1da07521f2",
        "date_manufacture": "2022-03-01T10:43:11.842Z",
        "date_expiration": "2023-05-20T10:43:20.431Z"
      },
      {
        "quantity": 200,
        "_id": "62af007abb78a63a44e88563",
        "date_manufacture": "2022-04-01T10:45:01.711Z",
        "date_expiration": "2023-05-11T10:45:06.882Z"
      }
    ]
  },
  {
    "_id": "62b3c2545a78fb4414dd718f",
    "locator": "62933e07c224b41fc48a1182",
    "imports": [
      {
        "quantity": 120,
        "_id": "62b3c2545a78fb4414dd7190",
        "date_manufacture": "2022-03-01T01:30:07.053Z",
        "date_expiration": "2023-05-01T10:43:20.431Z"
      }
    ]
  }
],
}

I want to decrease quantity in one locator by id in imports of inventory with multiple product (bulkWrite). And can I decrease quantity sort by date_expiration?

Example: when customer order product with quantity 300 and locator 62933b3fe744ac34445c4fc0, I want to product update belike:

{
...
"name": "Meat",
"price": 34400,
"inventory": [
  {
    "_id": "62af007abb78a63a44e88561",
    "locator": "62933b3fe744ac34445c4fc0",
    "imports": [
      {
        "quantity": 50,
        "_id": "62aefddcd5b52c1da07521f2",
        "date_manufacture": "2022-03-01T10:43:11.842Z",
        "date_expiration": "2023-05-20T10:43:20.431Z"
      }
    ]
  },
  {
    "_id": "62b3c2545a78fb4414dd718f",
    "locator": "62933e07c224b41fc48a1182",
    "imports": [
      {
        "quantity": 120,
        "_id": "62b3c2545a78fb4414dd7190",
        "date_manufacture": "2022-03-01T01:30:07.053Z",
        "date_expiration": "2023-05-01T10:43:20.431Z"
      }
    ]
  }
],
}

Thank you so much!

Answer 1

You should refactor your schema as nesting array as it is considered an anti-pattern and introduces unnecessary complexity to query.

One of the options:

db={
  "products": [
    {
      "_id": "62ab02ebd3e608133c947798",
      "status": true,
      "name": "Meat",
      "type": "62918ab4cab3b0249cbd2de3",
      "price": 34400,
      "inventory": [
        "62af007abb78a63a44e88561",
        "62b3c2545a78fb4414dd718f"
      ]
    }
  ],
  "inventory": [
    {
      "_id": "62af007abb78a63a44e88561",
      "locator": "62933b3fe744ac34445c4fc0",
      "imports": [
        {
          "quantity": 150,
          "_id": "62aefddcd5b52c1da07521f2",
          "date_manufacture": ISODate("2022-03-01T10:43:11.842Z"),
          "date_expiration": ISODate("2023-05-20T10:43:20.431Z")
        },
        {
          "quantity": 200,
          "_id": "62af007abb78a63a44e88563",
          "date_manufacture": ISODate("2022-04-01T10:45:01.711Z"),
          "date_expiration": ISODate("2023-05-11T10:45:06.882Z")
        }
      ]
    },
    {
      "_id": "62b3c2545a78fb4414dd718f",
      "locator": "62933e07c224b41fc48a1182",
      "imports": [
        {
          "quantity": 120,
          "_id": "62b3c2545a78fb4414dd7190",
          "date_manufacture": ISODate("2022-03-01T01:30:07.053Z"),
          "date_expiration": ISODate("2023-05-01T10:43:20.431Z")
        }
      ]
    }
  ]
}

You can then do something relatively simple. Use $sortArray to sort the date_expiration and start to iterate through the arrays using $reduce.

db.inventory.aggregate([
  {
    $match: {
      locator: "62933b3fe744ac34445c4fc0"
    }
  },
  {
    "$set": {
      "imports": {
        $sortArray: {
          input: "$imports",
          sortBy: {
            date_expiration: 1
          }
        }
      }
    }
  },
  {
    $set: {
      result: {
        "$reduce": {
          "input": "$imports",
          "initialValue": {
            "qtyToDecrease": 300,
            "arr": []
          },
          "in": {
            "qtyToDecrease": {
              $subtract: [
                "$$value.qtyToDecrease",
                {
                  $min: [
                    "$$value.qtyToDecrease",
                    "$$this.quantity"
                  ]
                }
              ]
            },
            "arr": {
              "$concatArrays": [
                "$$value.arr",
                [
                  {
                    "$mergeObjects": [
                      "$$this",
                      {
                        "quantity": {
                          $subtract: [
                            "$$this.quantity",
                            {
                              $min: [
                                "$$value.qtyToDecrease",
                                "$$this.quantity"
                              ]
                            }
                          ]
                        }
                      }
                    ]
                  }
                ]
              ]
            }
          }
        }
      }
    }
  },
  {
    $set: {
      imports: "$result.arr",
      result: "$$REMOVE"
    }
  },
  {
    "$merge": {
      "into": "inventory",
      "on": "_id"
    }
  }
])

Mongo Playground

---

Here is another version that keeps your original schema. You can see it is much more complex.