How do you update objects in a document's array (nested updating)

Question

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
            }
    ]
}

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

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

Answer 1

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);

Answer 2

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.

Answer 3

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
);

Answer 4

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!

1. Push into the document if "items.item_name":{"$ne":"my_name"} - the update filter MUST contain some uniquely indexed field! And upsert is false.
2. 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"