MacLochlainns Weblog

How do you avoid encountering a “duplicate key on update” in MongoDB? Well, that’s an interesting question if you’re coming at it from a relational perspective. The answer is understanding that while there may be a natural key, the update needs to use the unique identifier (or, _id). The unique identifier is essentially a primary key based on a system generated surrogate key, assigned by the insertOne() or insertMany() methods. As you’ll notice in the example, it’s not just a numeric value.

The following builds you a test case and is designed to run from the interactive mongo shell. It assumes you understand enough JavaScript to read the code dealing with the JSON (JavaScript Object Notation) structure.

1. Insert three rows in a people collection with the insertMany() method:

   > db.people.insertMany([ ... {"name" : "joe", "age" : 65} ... ,{"name" : "joe", "age" : 20} ... ,{"name" : "joe", "age" : 49}])

   > db.people.insertMany([ ... {"name" : "joe", "age" : 65} ... ,{"name" : "joe", "age" : 20} ... ,{"name" : "joe", "age" : 49}])

   it returns the following unique identifiers:

   { "acknowledged" : true, "insertedIds" : [ ObjectId("60a4a67f8d03d82365e994ab"), ObjectId("60a4a67f8d03d82365e994ac"), ObjectId("60a4a67f8d03d82365e994ad") ] }

   { "acknowledged" : true, "insertedIds" : [ ObjectId("60a4a67f8d03d82365e994ab"), ObjectId("60a4a67f8d03d82365e994ac"), ObjectId("60a4a67f8d03d82365e994ad") ] }

2. Assign the return value from a findOne() method against the people collection to a joe variable:

   > joe = db.people.findOne({"name" : "joe", "age" : 20})

   > joe = db.people.findOne({"name" : "joe", "age" : 20})

   Typically, you can print local variables in the mongo shell with the print command, like:

   print(joe)

   print(joe)

   it returns the following:

   [object BSON]

   [object BSON]

   The object is a Binary JSON object, which requires you use the following command:

   printjson(joe)

   printjson(joe)

   It prints the following JSON element:

   { "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 20 }

   { "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 20 }

3. Increment the age value from 20 to 21:

   > joe.age++

   > joe.age++

   You can show the change in the age member’s value by printing the JSON element with the printjson() function:

   { "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 }

   { "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 }

4. If you attempt to replace the JSON structure in the MongoDB collection with anything other than the following, you’ll raise a duplicate key error. You must reference the _id value to update the correct element of the collection. The following syntax works:

   db.people.replaceOne({"_id" : joe._id}, joe)

   db.people.replaceOne({"_id" : joe._id}, joe)

   it should return the following:

   { "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

   { "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

   You can verify that the change only affected the originally queried row with the following command:

   db.people.find()

   db.people.find()

   it should return the following:

   { "_id" : ObjectId("60a4a67f8d03d82365e994ab"), "name" : "joe", "age" : 65 } { "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 } { "_id" : ObjectId("60a4a67f8d03d82365e994ad"), "name" : "joe", "age" : 49 }

   { "_id" : ObjectId("60a4a67f8d03d82365e994ab"), "name" : "joe", "age" : 65 } { "_id" : ObjectId("60a4a67f8d03d82365e994ac"), "name" : "joe", "age" : 21 } { "_id" : ObjectId("60a4a67f8d03d82365e994ad"), "name" : "joe", "age" : 49 }

I thought this example may help others because the O’Reily’s MongoDB: The Definitive Guide example (on pages 35-37) uses a manually typed string, which doesn’t lend itself to a programmatic solutions. It also failed to clarify that value returned from the findOne() method and assigned to the local joe variable would be a BSON (Binary JSON) object; and it didn’t cover any means to easily convert the BSON to a JSON text. The reality is there’s no necessity to cast the BSON into a JSON object, also left out of the text. All that said, it’s still an excellent, if not essential, book for your technical library if you’re working with the MongoDB database.