How do you update objects in a document's array (nested updating)
MongodbMongodb Problem Overview
Assume we have the following collection, which I have few questions about:
{
"_id" : ObjectId("4faaba123412d654fe83hg876"),
"user_id" : 123456,
"total" : 100,
"items" : [
{
"item_name" : "my_item_one",
"price" : 20
},
{
"item_name" : "my_item_two",
"price" : 50
},
{
"item_name" : "my_item_three",
"price" : 30
}
]
}
-
I want to increase the price for "item_name":"my_item_two" and if it doesn't exists, it should be appended to the "items" array.
-
How can I update two fields at the same time? For example, increase the price for "my_item_three" and at the same time increase the "total" (with the same value).
I prefer to do this on the MongoDB side, otherwise I have to load the document in client-side (Python) and construct the updated document and replace it with the existing one in MongoDB.
This is what I have tried and works fine if the object exists:
db.test_invoice.update({user_id : 123456 , "items.item_name":"my_item_one"} , {$inc: {"items.$.price": 10}})
However, if the key doesn't exist, it does nothing. Also, it only updates the nested object. There is no way with this command to update the "total" field as well.
Mongodb Solutions
Solution 1 - Mongodb
For question #1, let's break it into two parts. First, increment any document that has "items.item_name" equal to "my_item_two". For this you'll have to use the positional "$" operator. Something like:
db.bar.update( {user_id : 123456 , "items.item_name" : "my_item_two" } ,
{$inc : {"items.$.price" : 1} } ,
false ,
true);
Note that this will only increment the first matched subdocument in any array (so if you have another document in the array with "item_name" equal to "my_item_two", it won't get incremented). But this might be what you want.
The second part is trickier. We can push a new item to an array without a "my_item_two" as follows:
db.bar.update( {user_id : 123456, "items.item_name" : {$ne : "my_item_two" }} ,
{$addToSet : {"items" : {'item_name' : "my_item_two" , 'price' : 1 }} } ,
false ,
true);
For your question #2, the answer is easier. To increment the total and the price of item_three in any document that contains "my_item_three," you can use the $inc operator on multiple fields at the same time. Something like:
db.bar.update( {"items.item_name" : {$ne : "my_item_three" }} ,
{$inc : {total : 1 , "items.$.price" : 1}} ,
false ,
true);
Solution 2 - Mongodb
There is no way to do this in single query. You have to search the document in first query:
If document exists:
db.bar.update( {user_id : 123456 , "items.item_name" : "my_item_two" } ,
{$inc : {"items.$.price" : 1} } ,
false ,
true);
Else
db.bar.update( {user_id : 123456 } ,
{$addToSet : {"items" : {'item_name' : "my_item_two" , 'price' : 1 }} } ,
false ,
true);
No need to add condition {$ne : "my_item_two" }
.
Also in multithreaded enviourment you have to be careful that only one thread can execute the second (insert case, if document did not found) at a time, otherwise duplicate embed documents will be inserted.
Solution 3 - Mongodb
We can use $set
operator to update the nested array inside object filed update the value
db.getCollection('geolocations').update(
{
"_id" : ObjectId("5bd3013ac714ea4959f80115"),
"geolocation.country" : "United States of America"
},
{ $set:
{
"geolocation.$.country" : "USA"
}
},
false,
true
);
Solution 4 - Mongodb
One way to ensure there are no duplicates of the "item_name" fields would be the do the same actions as given in the answer https://stackoverflow.com/a/10523963 for Question #1 but in reverse order!
- Push into the document if "items.item_name":{"$ne":"my_name"} - the update filter MUST contain some uniquely indexed field! And upsert is false.
- Increment the document if "items.item_name":"my_name".
The first update should be atomic, thus it doesn't do anything if the array already contains the element with the item_name "my_name".
By the time the second update happens, there must be an array element with the "item_name"="my_name"