findAndModify to upsert document, and add or update element in array at the same time

865 views

Skip to first unread message

Nicolas Joyard

unread,

Mar 8, 2014, 1:36:06 PM3/8/14

to mongod...@googlegroups.com

Hello,

I'm having an issue I'm sure others have encountered but I can't find the right solution. I have a collection with documents following this model:

{
id: "id",
field: "value",
items: [
{ id: "itemid", field: "value", ... },
...
]
}

My app gets notifications about new or updated items and I want to update the collection accordingly. Those notifications don't tell whether the item is actually new or just updated. So I would like to perform a findAndModify that :

- if no matching document exists, create one with the item

- if a matching document exists, but the item is not in it, add it to the array

- if a matching document exists, and the item is in the array, update it

I want a findAndModify because item notifications can happen very often, so to avoid conflicts or race conditions I want an atomic update.

I tried the following (not the exact syntax but you get the idea):

collection.findAndModify({
query: { id: item.collectionId, items: { $elemMatch: { id: item.itemId } } },
update: {
$setOnInsert: {
id: item.collectionId,
field: item.fieldValue,
items: [ { id: item.itemId, field: item.itemField } ]
},
$set: {
"items.$.field": item.itemField
}
},
upsert: true
});

Unfortunately I get a "have conflicting mods in update" error. I guess this is because I update items both in $set and $setOnInsert, but I couldn't find any other update command that works. I wish there were a $setOnUpdate operator...

Does anyone have an idea on how to achieve what I want atomically ?

Thanks,

Nicolas

Glenn Maynard

unread,

Mar 10, 2014, 11:31:34 AM3/10/14

to mongod...@googlegroups.com

On Sat, Mar 8, 2014 at 12:36 PM, Nicolas Joyard <joyard....@gmail.com> wrote:

{
id: "id",
field: "value",
items: [

{ id: "itemid", field: "value", ... },
...
]

}

My app gets notifications about new or updated items and I want to update the collection accordingly. Those notifications don't tell whether the item is actually new or just updated. So I would like to perform a findAndModify that :

- if no matching document exists, create one with the item
- if a matching document exists, but the item is not in it, add it to the array
- if a matching document exists, and the item is in the array, update it

I want a findAndModify because item notifications can happen very often, so to avoid conflicts or race conditions I want an atomic update.

You're trying to upsert at the array level, just like how upserts work with documents. I hit this constantly. Unfortunately, it's not possible to do this atomically.

https://jira.mongodb.org/browse/SERVER-6566 would allow this, and in my opinion is one of the most important missing features. (Search for "upsert for arrays".)

I end up doing this with a two-step update. I add a sequence number to the top-level document, which I increment each time the document is modified, then use it to prevent race conditions, along these lines:

while True:

record = collection.find({_id: _id})

existing_item = find_record(record['items'], itemId)

changes = { $inc: { sequence: 1 } }

if existing_item != -1:

# The item already exists, so update it.

changes['$set'] = {

'items.%i' % existing_item: new_item
} else:

# The item doesn't exist, so add it.

changes['$push'] = {

items: new_item

}

if not collection.update({_id: record['_id'], sequence: record['sequence']}, changes):

# We couldn't find the record, so it was either updated between our find and update (sequence changed)

# or it no longer exists. Start over.

continue

# We updated the record, so stop.

break

(I didn't include the case of creating the initial document when it doesn't exist, eg. record is null. This is just demonstrating the update portion.)

This works, but I don't like it. It's a lot more complicated than it should need to be. It's also hard to prove that there are no failure cases that won't busy loop, so in practice I only let it try twice, then throw an exception. (It won't normally loop forever, since no matter how many clients are competing to update the same record, at least one client will successfully update each time through.)

Make sure you increment sequence whenever the array changes, such as if you delete records.

You can use findAndModify with this, of course. I do almost all non-batch updates with findAndModify, since I always want to update my loaded copy of documents after making a change.

I tried the following (not the exact syntax but you get the idea):

collection.findAndModify({

query: { id: item.collectionId, items: { $elemMatch: { id: item.itemId } } },
update: {
$setOnInsert: {

setOnInsert is only used when a new top-level document is being created, so while it can help the case where the document doesn't exist at all, it doesn't help the harder problem of updating existing documents.