Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MongoDB says duplicate key, but the value is null

ErrorE11000 duplicate key error collection: mydb.users index: email_1 dup key: { email: null }

Short answer

A unique index in MongoDB stores a missing field as null, and only one null entry is allowed. The second document without that field fails with E11000, even though the key shows null. Drop the index and recreate it as a unique partial index on real values, or stop saving null.

  • Applies to MongoDB 7, MongoDB 8, Node.js 22, mongosh 2
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the index name and the dup key from the error, then list the indexes and count documents where that field is null or missing.

    db.users.getIndexes()
    db.users.countDocuments({ email: null })
  2. 2Match what you see, then jump to its fix

    1. 1dup key: { email: null } and your new documents have no email at allSeveral documents omit the field, and the index is uniqueFix
    2. 2The index is already unique and sparse, yet the error still shows dup key: { email: null }Documents store an explicit null, so a sparse unique index does not helpFix
    3. 3The error appears on createIndex or on app start, not on an insertExisting duplicates make the index build failFix

What does the error look like?

  • E11000 duplicate key error collection: mydb.users index: email_1 dup key: { email: null }
  • MongoServerError: E11000 duplicate key error

Cause 1 of 3

Several documents omit the field, and the index is unique

1Check

In a unique single-field index, a missing field counts as a null entry, and a second one is a duplicate. The index list shows whether it is unique.

db.users.getIndexes()

2Fix

Replace the index with a unique partial index that only covers documents where the field is a real string. Missing and null values are then not indexed and cannot clash.

db.users.dropIndex("email_1")
db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $type: "string" } } }
)

3Verify

Insert two documents without email; both succeed. A repeated real email still fails.

db.users.insertOne({ name: "A" })
db.users.insertOne({ name: "B" })

Confirmed by mongodb.com

Did this fix it?

Cause 2 of 3

Documents store an explicit null, so a sparse unique index does not help

1Check

A sparse index skips documents that lack the field, but a field set to null is present and is indexed. Two documents with email: null then clash. Count them.

db.users.countDocuments({ email: { $type: "null" } })

2Fix

Remove the null values so the field is truly missing, and make the application stop writing null. Or use the partial index from the first cause, which skips nulls too.

db.users.updateMany(
  { email: { $type: "null" } },
  { $unset: { email: "" } }
)

3Verify

No document keeps a null email.

db.users.countDocuments({ email: { $type: "null" } })

Confirmed by mongodb.com

Did this fix it?

Cause 3 of 3

Existing duplicates make the index build fail

1Check

MongoDB cannot create a unique index when the existing data already violates it, and more than one null entry fails the build. Find the repeated values first.

db.users.aggregate([
  { $group: { _id: "$email", n: { $sum: 1 } } },
  { $match: { n: { $gt: 1 } } }
])

2Fix

If the repeated value is null, build the partial index below, which leaves null and missing values out. If it is a real value, clean or merge those documents first, then build the index.

db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $type: "string" } } }
)

3Verify

The index appears in the list.

db.users.getIndexes()

Confirmed by mongodb.com

Did this fix it?

How do I stop it coming back?

  • Decide per optional unique field: partial index on string values, or never write null.
  • Do not combine sparse and partialFilterExpression on one index; MongoDB does not allow it.
  • Test unique indexes in staging with documents that omit the field.
  • Build indexes in a migration, not from application start-up.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.