Decrement a field in mongodb using a field from the same document

Question

I have a collection of store products, where each document is a product that is associated with a store and their locations, in addition to the quantity of that product for that store. The document schema looks as follows:

Sample Doc :

{
    product         : 'Mobile Phone',
    store           : 'Electronics Store',
    total_quantity  : 5,
    locations       : [ { id: 'loc1', quantity: 3 }, { id: 'loc2', quantity: 2 } ]
}

I want to be able to remove a location from all the store products by the location id, while also updating the total quantity.

I know I can do this by getting each document and updating it, but this requires queries equal to the amount of the products that has that location. Hence, I was thinking of trying to achieve this by doing two queries:

An update query the matches the location id and decrements the total quantity based on it in one go
A remove query that pulls the location from the locations array

The problem here is I do not know how to achieve the first step or if it is even possible. Any other suggestions are more than welcome.

Does this answer your question ? https://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field — whoami - fakeFaceTrueSoul, May 05 '20 at 05:37
Unfortunately not, since I am not using the $set operator. I am using $inc to increment the quantity, and when I try doing $inc : {quantity : "$field"} it results in a type error. — Fayad, May 05 '20 at 06:01
What is your MongoDB version ? Also when you say wanted to remove a location then what is the input to query to find that location from `locations` array ? Additionally what quantity do you wanted to increment ? — whoami - fakeFaceTrueSoul, May 05 '20 at 06:03
I am using mongodb (v4.2.0). The find query is as follows {store : 'Electronics Store', locations : { $elemMatch: { id: 'loc1' } } }. I have update my question to be a bit clearer in terms of which quantity I want to update (total quantity). — Fayad, May 05 '20 at 06:11
So you wanted to updated `total_quantity` with sum of all quantities remaining in locations array ? then I would say it will be decrement since you're pulling values out from locations what do you mean by increment ? — whoami - fakeFaceTrueSoul, May 05 '20 at 06:20
Yes I mean to decrement using the increment operator $inc which you can just give a negative value and achieve the same behaviour, since I am not sure there is another way of doing so. — Fayad, May 05 '20 at 06:25

score 1 · Accepted Answer · answered May 05 '20 at 06:35

As you can execute update-with-an-aggregation-pipeline in .update() operations starting MongoDB version >= 4.2, try below queries :

Query 1 : This query re-creates total_quantity field by subtracting locations.quantity of particular element being removed from existing total_quantity. Also re-creates locations array without element which needs to be removed.

db.collection.updateMany({'locations.id': 'loc1'},
  [
    { $addFields: { total_quantity: { $subtract: [ "$total_quantity", { $arrayElemAt: [ "$locations.quantity", { $indexOfArray: [ "$locations.id", "loc1" ] } ] } ] } } },
    { $addFields: { locations: { $filter: { input: "$locations", cond: { $ne: [ "$$this.id", "loc1" ] } } } } }
  ]
)

Test : Test aggregation pipeline here : mongoplayground

Query 2 :

This query first re-creates locations array without element which needs to be removed & then iterates through leftover array locations.quantity to sum-up all the quantity values of all elements in locations array to create total_quantity field.

db.collection.updateMany({'locations.id': 'loc1'},
  [
    { $addFields: { locations: { $filter: { input: "$locations", cond: { $ne: [ "$$this.id", "loc1" ] } } } } },
    { $addFields: { total_quantity: { $reduce: { input: "$locations.quantity", initialValue: 0, in: { $add: [ "$$value", "$$this" ] } } } } }
  ]
)

Test : Test aggregation pipeline here : mongoplayground

Note : If you find any issue executing these queries with .updateMany() try .update() with an option { multi : true }.

Decrement a field in mongodb using a field from the same document

1 Answers1