MongoDB insertOne and insertMany: Adding Documents the Right Way

MongoDB insertOne adds one document to a collection, and insertMany adds a whole list in a single call. Both look simple until one document in the list fails. This post tests what lands and what does not when a duplicate key sits in the middle of a batch.

A Collection With a Unique Index

I ran every example below on MongoDB 9.0.2 with mongosh 2.13.0. If you need a server first, my post on how to install MongoDB on Windows covers the setup. The collection is called products, and each product has a sku, which is a short stock code. To make a failure possible, we add a unique index on sku.

A unique index works like a UNIQUE constraint in SQL Server. It rejects a second document that carries a value the collection already holds. The createIndex command returns the name of the new index, and it creates the database and the collection too.

use insertDemo
db.products.createIndex({ sku: 1 }, { unique: true })
// sku_1

insertOne and the Generated _id

MongoDB insertOne takes one document. When the document has no _id field, MongoDB adds one before it stores the document. The generated value is an ObjectId, a 12-byte value that is unique in practice. The result reports it as insertedId.

db.products.insertOne({ sku: 'A100', name: 'Basmati rice', price: 4.99 })
// { acknowledged: true, insertedId: ObjectId('...') }

You can also supply your own _id, such as an order number. That fits when the business already has a natural unique key. MongoDB then stores your value as it is. Letting MongoDB generate it is simpler, because the value is unique without any coordination between clients.

The dots in the result stand for a value that changes on every run. In my test, I checked that it was an ObjectId. Now insert a different product with the same sku. The server refuses it, and the shell throws an error. The code below catches the error so we can print its parts.

try { db.products.insertOne({ sku: 'A100', name: 'Brown rice', price: 3.79 }); } catch (e) { printjson({ name: e.name, code: e.code, message: e.message }); }
// {
//   name: 'MongoServerError',
//   code: 11000,
//   message: 'E11000 duplicate key error collection: insertDemo.products index: sku_1 dup key: { sku: "A100" }'
// }
db.products.countDocuments({})
// 1

The code 11000 means duplicate key. The message names the collection, the index and the value that clashed. The count is still 1, so the failed insert left nothing behind. Before the next test, we empty the collection with deleteMany. The unique index stays in place.

db.products.deleteMany({})

insertMany Takes a List

The insertMany method takes an array of documents and sends them to the server together. Each document without an _id gets its own ObjectId. The result lists the ids by position in your array. SQL Server people can read it as a multi-row INSERT with VALUES, where MongoDB also hands back the new keys.

db.products.insertMany([
  { sku: 'B100', name: 'Rolled oats', price: 3.29 },
  { sku: 'B101', name: 'Whole wheat flour', price: 2.99 }
])
// { acknowledged: true, insertedIds: { '0': ObjectId('...'), '1': ObjectId('...') } }
db.products.deleteMany({})

In my test, the result held two ids, and both were ObjectId values. The documents did not repeat any sku, so every one of them was stored. A long list is not a problem for the shell. The driver splits it into smaller groups before it sends it.

A Duplicate in the Middle of the Batch

Now the real question. Here is a batch of five products. The third one repeats the sku of the second one. We keep the batch in a variable so we can use it twice.

let batch = [
  { sku: 'A100', name: 'Basmati rice', price: 4.99 },
  { sku: 'A101', name: 'Red lentils', price: 2.49 },
  { sku: 'A101', name: 'Black beans', price: 1.79 },
  { sku: 'A102', name: 'Chickpeas', price: 1.99 },
  { sku: 'A103', name: 'Carrots', price: 1.49 }
]

In mongosh, a failed insertMany throws a MongoBulkWriteError. This small function runs an insert, catches that error and prints the parts that matter. It ends by listing the skus that are in the collection.

function tryInsert(docs, opts) {
  try { db.products.insertMany(docs, opts); }
  catch (e) {
    printjson({ name: e.name, code: e.code, insertedCount: e.result.insertedCount });
    printjson(e.writeErrors.map(w => ({ index: w.index, errmsg: w.errmsg })));
  }
  printjson(db.products.find({}, { _id: 0, sku: 1 }).sort({ sku: 1 }).toArray().map(d => d.sku));
}

The default is an ordered insert, which has the option ordered: true. MongoDB inserts the documents in list order and stops at the first error. Run it.

tryInsert(batch)
// { name: 'MongoBulkWriteError', code: 11000, insertedCount: 2 }
// [
//   {
//     index: 2,
//     errmsg: 'E11000 duplicate key error collection: insertDemo.products index: sku_1 dup key: { sku: "A101" }'
//   }
// ]
// [ 'A100', 'A101' ]

Two documents landed and the third failed. The documents at positions 3 and 4 were never tried, even though they were fine. The first two stayed in the collection. The index in the error is the position in your array, so you can find the bad document.

Now empty the collection and run the same batch with ordered: false. MongoDB then tries every document and collects the errors.

db.products.deleteMany({})
tryInsert(batch, { ordered: false })
// { name: 'MongoBulkWriteError', code: 11000, insertedCount: 4 }
// [
//   {
//     index: 2,
//     errmsg: 'E11000 duplicate key error collection: insertDemo.products index: sku_1 dup key: { sku: "A101" }'
//   }
// ]
// [ 'A100', 'A101', 'A102', 'A103' ]

Four documents landed, and the one error is the same. The two documents after the duplicate made it in. With ordered: false, the error list can hold more than one entry. This batch repeats two skus that are already stored.

tryInsert([
  { sku: 'A104', name: 'Spinach', price: 2.29 },
  { sku: 'A100', name: 'Basmati rice again', price: 4.99 },
  { sku: 'A105', name: 'Apples', price: 1.99 },
  { sku: 'A101', name: 'Red lentils again', price: 2.49 }
], { ordered: false })
// { name: 'MongoBulkWriteError', code: 11000, insertedCount: 2 }
// [
//   { index: 1, errmsg: 'E11000 duplicate key error collection: insertDemo.products index: sku_1 dup key: { sku: "A100" }' },
//   { index: 3, errmsg: 'E11000 duplicate key error collection: insertDemo.products index: sku_1 dup key: { sku: "A101" }' }
// ]
// [ 'A100', 'A101', 'A102', 'A103', 'A104', 'A105' ]

Two new products landed, and two repeats were reported with their positions. This also shows what happens when you retry a batch after a partial failure. The unique index blocks the documents that already landed, so nothing is stored twice.

How This Differs From SQL Server

The last row is the surprise. MongoDB insertOne matches a single-row INSERT. A multi-row INSERT in SQL Server is one statement, so a duplicate rolls back all of its rows. An insertMany is not one unit of work. Documents that were stored before the error stay stored. If you need all or nothing, MongoDB offers multi-document transactions for that job. My post on SQL terms vs MongoDB terms covers more of the vocabulary gap.

You could say ordered: false is always better, since more documents land. Fair point. It suits independent records, such as a load of unrelated products. But a half-loaded batch is a state your code must handle. Ordered inserts give you a clear stopping point, which helps when later documents depend on earlier ones. A load of order lines, where each line needs its parent order, is a good example.

A Simple Rule

Use MongoDB insertOne for single writes and insertMany for lists. Pick ordered when order matters, and unordered when the documents are independent. In both cases, compare insertedCount with the length of your array. Log the writeErrors list with the positions. Never assume that a thrown error means nothing was stored. After a failure, read the count before you retry, and let the unique index protect you from double inserts. When you finish testing, remove the example database.

use insertDemo
db.dropDatabase()

MongoDB insertMany is not all or nothing, it is as far as it got, plus a list of what failed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.