node.jsmongodbaggregatebulkbulkupdate

How to bulkWrite decrease quantity in products when order mongodb?


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!


Solution

  • 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.