'upsert' in an embedded document

Question

I currently have the following dataset:

{  
    'component_id':1,  
    '_locales':[   
        {  
            'url': 'dutch',  
            'locale': 'nl_NL'  
        } 
    ] (etc)
}

If I want to update the row with the locale I would run something similar to:

db.components.update(
    {'component_id': 1, '_locales.locale': 'nl_NL'},
    {$set: {'_locales.$': {'url': 'new url','locale':'nl_NL'}}, 
    true
);

This works fine untill the locale does not exists:

db.components.update(
    {'component_id': 1, '_locales.locale': 'en_US'},
    {$set: {'_locales.$': {'url': 'new url','locale':'en_US'}}, 
    true
);

since there is a unique index on component_id this will throw an exception complaining about a duplicate key.

Is there a way to automatically add the new 'document' with a different locale and update it if it already exists? According to the documentation using the position operator will not work with 'upserting'.

Methacrylate

Answer 1

You can use $addToSet to add to a set making sure there is no duplicate array element, but that will not work for your "updating" case.

In order to do what you want, you will need to change your data structure to something like:

{
    "_id" : ObjectId("4f9519d6684c8b1c9e72e367"),
    "component_id" : 1,
    "_locales" : {
        "nl_NL" : {
            "url" : "dutch"
        }
    }
}

Now you can do an update on the nl_NL locale with just:

db.components.update( { component_id: 1 }, { $set: { '_locales.nl_NL.url' : 'new url' } }, true );

And a new locale will work as well, such as with:

db.components.update( { component_id: 1 }, { $set: { '_locales.en_US.url' : 'American' } }, true );

You might want to consider to having the locale as part of the nested object as well perhaps, like in:

{
    "_id" : ObjectId("4f9519d6684c8b1c9e72e367"),
    "component_id" : 1,
    "_locales" : {
        "nl_NL" : {
            "url" : "dutch"
            "locale" : "nl_NL"                 
        }
    }
}

This makes it easier to retrieve data in some cases.

Byronbyrum

Hi Derick, thank you for replying. Your suggestion was actually my initial datastructure which I changed to the above. One reason was creating the indexes on: _locales.url rather than on every locales: _locales.nl_NL.url, _locales.en_US.url etc. Currently I solved this by obtaining all the _locales data and modify/add the locale I'm working with 'manually'. WHen I'm done I replace the current '_locales' with the new one. For now this will suffice, performance wise this might not be a very good idea. – Methacrylate

I do agree, having a "non-defined" key is not often a good thing to do due to the indexes. Sometimes, it's just better to do two queries to do that update if that improves performance due to indexes/other reasons in other cases. There is no real "correct way" most of the time. Play with it, and if it doesn't perform change it back and run two queries for updating. – Byronbyrum

@Derick: I'm facing a similar issue. #32039106 – Mailbox

But running two queries is not atomic. You will be facing the need of locking the record outside and overcomplicate the solution. 10 years later and I am also struggling implementing this. – Derris