A novice mongodb developer here. I'm trying to write an update query where I search for stocks of certain products and decrease the quantities based on how many products are being sold. I have a products list which is a list of objects, it contains the products that are being sold.

products= [
  {
    product: "*some product id*",
    quantity: "*some quantity*"
  }
]

I extract product ids from the above list into a productIds list. And below is how my query looks like. I have realized I can't pass "$product" [a field of the stock] into the indexof(), is there a way to handle this using update with an aggregation pipeline? If not what other options do I have ? I have searched the documentation but couldn't find anything helpful.

Stock.updateOne(
      {
        product: { $in: productIds },
        quantity: { $gt: 0 },
      },
      [
        {
          $set: {
            newQuantity: {
              $subtract: [
                "$quantity",
                products[productIds.indexOf("$product")].quantity,
              ],
            },
          },
        },
      ],
    );

CodePudding user response：

Query

• $reduce to find the quantity to subtract
• listQuantity will be the that quantity, product is compared with the product of the list, and if match, we get the quantity (as reduce result)
• instead of "input": [{"product":1, "quantity":3}, {"product":2, "quantity":4}] use "input": YOUR_JS_ARRAY_VARIABLE
• and then subtract this quantity from the previous one
• productIds must be only the [1,2] in this example

Test code here

Stock.update(
{
  "product": { "$in": productIds },
  "quantity": { "$gt": 0 },
}
,
[{"$set":
  {"listQuantity":
   {"$reduce":
    {"input":
     [{"product":1, "quantity":3}, {"product":2, "quantity":4}],
     "initialValue":-1,
     "in":
     {"$cond":
      [{"$and":
        [{"$eq":["$$value", -1]},
         {"$eq":["$$this.product", "$product"]}]},
       "$$this.quantity", "$$value"]}}}}},
 {"$set":
  {"quantity":
   {"$cond":
    [{"$eq":["$listQuantity", -1]}, "$quantity",
     {"$subtract":["$quantity", "$listQuantity"]}]},
   "listQuantity":"$$REMOVE"}}])

CodePudding user response：

Here is an example of the dataset of the Order Collection that I used to write my query

{
  "name" : "Order 2",
  "products" : [
    {"idProduct" : "61c5ec036d9383e03e62d8ae" , "quantity" : 6 },
    {"idProduct" : "61c5ebee6d9383e03e62d8ac" , "quantity" : 2 } ]
}

Here is my aggregate on the Order Collection to update products on the Product Collection

[{$unwind: {
  path: "$products"
}}, {$lookup: {
  from: 'Product',
  localField: 'products.idProduct',
  foreignField: '__id',
  as: 'productsToAdjust'
}}, {$unwind: {
  path: "$productsToAdjust"
}}, {$set: {
  "productsToAdjust.quantity":  { $subtract :["$productsToAdjust.quantity","$products.quantity"] }
}}, {$replaceRoot: {
  newRoot: "$productsToAdjust"
}}, {$merge: {
  into: 'Product',
  on: '__id',
  whenMatched: 'merge',
  whenNotMatched: 'fail'
}}]