Upsert Array Elements matching criteria in a MongoDB document?

Question

As per How do I update Array Elements matching criteria in a MongoDB document?

I want to upsert the array elements, so if one doesnt match then insert it, otherwise update it.

I tried the answer on that question, and it works fine IF the array element already exists. If the element doesnt exist then it creates a child of "$" under the array field.

My Mongo structure is as follows:

Widget (collection)
--Name
--Properties (array)
  --Name
  --Value

My application gets a Widget Name and a list of Properties from a WebService call. I wish to iterate the provided Properties and update the value in the MongoDB if the Name already exists, OR insert a new Property to the Properties array if it doesnt.

score 10 · Accepted Answer · answered Jan 16 '12 at 09:39

What you require is not possible using a single update without some app-side logic. Note that upsert as a feature is not relevant for this specific problem unless you want to automatically create new Widget documents if none exist with the provided name.

The problem you're running into is that there is no functionality that allows you to do two different updates depending on the existence of an array element. Your only two options are :

Find the item, determine existence of relevant propertie(s), compile an appropriate update with your new or changes properties and execute it. This comes with the important downside that this is not a concurrency safe method. In other words, if two web services attempt this at the same one could overwrite eachother's changes.
Make widget properties top level documents rather than embedded. Allows you to use upserts to do what you want. Obvious downside is that that isn't a very nice option in terms of schema design. You wouldn't automatically get all properties if you fetch a widget, for example.

I had also come to this conclusion. I have approx 3500 properties per widget, and likely over 100,000 widgets. Is it a sensible design to have the properties in their own collection? — justacodemonkey, Jan 16 '12 at 10:05
"Upserts" are possible if you can restructure your document. See my answer. — G-Wiz, Jan 25 '14 at 05:29

G-Wiz · Answer 2 · 2014-01-25T05:42:21.457

You cannot atomically upsert array elements. But if you can restructure your document to use an object instead of an array, then this is atomically possible. Using your notation, the structure would be

Widget (collection)
  --Name
  --Properties
    --Property1
    --Property2
        .
        :

An example document would be

{
    name: 'widget-1',
    properties: {
        'property-1': 100,
        'property-2': 200
    }
}

To upsert an item named 'property-3' with value 300, you'd do

db.widgets.update(
    { name: 'widget-1' }, 
    { $set: { 'properties.property-3': 300 } }
)

One drawback is that querying for field names (using the $exists query operator) requires scanning. You can get around this by adding an extra array field that stores the property names. This field can be indexed and queried normally. Upserts become

db.widgets.update(
    { name: 'widget-1' }, 
    { 
        $set: { 'properties.property-3': 300 },
        $addToSet: { propertyNames: 'property-3' }
    }
)

(Keep in mind $addToSet is O(n).) When removing a property, you have to pull it from the array as well.

db.widgets.update(
    { name: 'widget-1' }, 
    { 
        $unset: { 'properties.property-3': true },
        $pull: { propertyNames: 'property-3' }
    }
)

This is not without its downsides, though. For example, you cannot index fields in the elements. Also, there are various tools and type systems that don't support this concept very well. For example, if you want to map this type to GraphQL you will be out of luck, it doesn't support dynamic keys in objects. — Dobes Vandermeer, Oct 15 '19 at 00:32
True. If you absolutely need atomic upserts and are exposing the collection with GraphQL you’ll need to translate the object structure into an array (e.g. with Apollo by using a custom resolver). — G-Wiz, Oct 15 '19 at 17:29
As of mongodb 4.2 you can also do an atomic update using a pipeline to upsert into an array, from what I understand. — Dobes Vandermeer, Oct 16 '19 at 18:05

score 1 · Answer 3 · edited Feb 19 '13 at 20:50

1

At least in php it works for me, try to adopt to your language. You need your collection look like:

        {Name: x, Properties: {name1:value1, name2:value2, name3:value3}}

        foreach ($Properties as $name => $value)
        {
            $propertiesArray["Properties.$name"] = $value;                
        }   

        $criteria  = array('Name' => $name);
        $operation = array('$set'   => $propertiesArray);
        $options   = array('upsert' => true);

        $collection = $this->_dbh->selectCollection('Widget');
        $collection->update($criteria, $operation, $options);

It inserts new names if not exist and updates those which already there

edited Feb 19 '13 at 20:50

spajce

7,044
5
29
44

answered Feb 19 '13 at 20:29

Slava Elantsev

81
1
6

This is correct but you didn't really explain why this works. The asker needs to restructure their document to use an object instead of array. See my answer for more details. (Also, your use of the upsert flag will upsert the whole document, but the question was specifically about array elements.) – G-Wiz Jan 25 '14 at 05:30

Upsert Array Elements matching criteria in a MongoDB document?

3 Answers3

Linked