mongodb data_error ai_generated true

MongoServerError: Index build failed: collection mydb.mycoll has duplicate keys for index 'unique_idx'

ID: mongodb/index-build-failed-due-to-duplicate-keys

Also available as: JSON · Markdown · 中文
90%Fix Rate
87%Confidence
1Evidence
2023-06-05First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MongoDB 6.0 active — — —
MongoDB 7.0 active — — —
MongoDB 8.0 active — — —

Root Cause

An attempt to create a unique index failed because the collection contains documents with duplicate values for the indexed field(s).

generic

中文

尝试创建唯一索引失败，因为集合包含索引字段的重复值。

Workarounds

  1. 90% success Identify duplicate documents using aggregation: db.mycoll.aggregate([ { $group: { _id: '$field', count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ]). Then delete or update duplicates. 
    Identify duplicate documents using aggregation: db.mycoll.aggregate([ { $group: { _id: '$field', count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ]). Then delete or update duplicates.
  2. 85% success Use db.collection.createIndex() with { unique: true, partialFilterExpression: { field: { $exists: true } } } to skip documents missing the field if duplicates are only on missing values. 
    Use db.collection.createIndex() with { unique: true, partialFilterExpression: { field: { $exists: true } } } to skip documents missing the field if duplicates are only on missing values.
  3. 80% success Create a non-unique index first, then manually deduplicate data and rebuild the unique index. 
    Create a non-unique index first, then manually deduplicate data and rebuild the unique index.

中文步骤

  1. Identify duplicate documents using aggregation: db.mycoll.aggregate([ { $group: { _id: '$field', count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ]). Then delete or update duplicates.
  2. Use db.collection.createIndex() with { unique: true, partialFilterExpression: { field: { $exists: true } } } to skip documents missing the field if duplicates are only on missing values.
  3. Create a non-unique index first, then manually deduplicate data and rebuild the unique index.

Dead Ends

Common approaches that don't work:

  1. 100% fail

    The same duplicates still exist in the collection.

  2. 90% fail

    dropDups is removed in MongoDB 4.0+; in older versions, it deletes arbitrary duplicates.

  3. 80% fail

    Sparse indexes still enforce uniqueness on non-null values, so duplicates remain.