CODEWITHSUNDEEP
mongodbmongodb-queryconditional-statementsupsertsql-update
karlitos
karlitos

Reputation: 1656

Conditional upsert (insert) when updating document in MongoDB

I have some documents in a MongoDB which looks like that:

{type: type1, version: 2, data: ...}
{type: type1, version: 3, data: ...}
{type: type2, version: 1, data: ...}
{type: type2, version: 2, data: ...}
...

I would like to update the data for matching type AND version or create a new document for given type when version mismatch but would like to prohibit creating new documents with new type When I do:

db.getCollection('products').update({"type": "unknown_type", "version" : "99"}, {$set: {"version": 99, "data": new data}}, {"upsert": true})

it creates a new document:

{type: unknown_type, version: 99, data: ...}

which is exactly what I would like to prohibit. Is there a way to do this operation in one call ? Is there a way to restrict values for some fields ?

Upvotes: 4

Views: 4133

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151072

The best handling I can see for this use case is using "Bulk Operations" in order to send both "update" and "insert" commands in the same request. We also need to have a unique index in here to enforce that you actually do not create new combinations of the two fields.

Starting with these documents:

{ "type" : "type1", "version" : 2 }
{ "type" : "type1", "version" : 3 }
{ "type" : "type2", "version" : 1 }
{ "type" : "type2", "version" : 2 }

And creating a unique index on the two fields:

db.products.createIndex({ "type": 1, "version": 1 },{ "unique": true })

Then we try and do something that will actually insert, using the bulk operations for both the update and the insert:

db.products.bulkWrite(
  [
     { "updateOne": {
       "filter": { "type": "type3", "version": 1 },
       "update": { "$set": { "data": {} } }
     }},
     { "insertOne": {
       "document": { "type": "type3", "version": 1, "data": { } }
     }}
  ],
  { "ordered": false }
)

We should get a response like this:

{
        "acknowledged" : true,
        "deletedCount" : 0,
        "insertedCount" : 1,
        "matchedCount" : 0,
        "upsertedCount" : 0,
        "insertedIds" : {
                "1" : ObjectId("594257b6fc2a40e470719470")
        },
        "upsertedIds" : {

        }
}

Noting here the matchedCount was 0 reflecting the "update" operation:

        "matchedCount" : 0,

If I did the same thing again, with different data:

db.products.bulkWrite(
  [
     { "updateOne": {
       "filter": { "type": "type3", "version": 1 },
       "update": { "$set": { "data": { "a": 1 } } }
     }},
     { "insertOne": {
       "document": { "type": "type3", "version": 1, "data": { "a": 1 } }
     }}
  ],
  { "ordered": false }
)

Then we see:

BulkWriteError({
        "writeErrors" : [
                {
                        "index" : 1,
                        "code" : 11000,
                        "errmsg" : "E11000 duplicate key error collection: test.products index: type_1_version_1 dup key: { : \"type3\", : 1.0 }",
                        "op" : {
                                "_id" : ObjectId("5942583bfc2a40e470719471"),
                                "type" : "type3",
                                "version" : 1,
                                "data" : {
                                        "a" : 1
                                }
                        }
                }
        ],
        "writeConcernErrors" : [ ],
        "nInserted" : 0,
        "nUpserted" : 0,
        "nMatched" : 1,
        "nModified" : 1,
        "nRemoved" : 0,
        "upserted" : [ ]
})

Which is going to consistently throw an error in all drivers, but we can also see in the detail of the response:

        "nMatched" : 1,
        "nModified" : 1,

Which means that even though the "insert" failed, the "update" actually did it's job. The important thing to note here is that whilst "errors" can occur in the "batch", we can handle them when they are of the type predicted, which is the 11000 code for duplicate key errors that we expected.

So the end data of course looks like:

{ "type" : "type1", "version" : 2 }
{ "type" : "type1", "version" : 3 }
{ "type" : "type2", "version" : 1 }
{ "type" : "type2", "version" : 2 }
{ "type" : "type3", "version" : 1, "data" : { "a" : 1 } }

Which is what you wanted to achieve here.

So the operations will produce an exception, but by marking as "unordered" with the { "ordered": false } option to .bulkWrite() then it will at least commit any instructions that did not result in an error.

In this case, the typical result is that either the "insert" works and there is no update, or the "insert" fails where the "update" applies. When the fail is returned in the response, you can check the "index" of the error is 1 indicating the expected "insert" fail and that the error code is 11000 because of the expected "duplicate key".

The errors in the "expected" case can therefore be ignored and you would only need handle the "unexpected" errors for a different code an/or different position in the issued bulk instruction.

Upvotes: 3

Related Questions