Finding Duplicate Keys with MongoDB’s Aggregation Framework

Quite frequently our users want to create a unique index on a data set but encounter some form of the following error because of duplicate key value(s):

E11000 duplicate key error index: db.collection.$field_1_field2_1  dup key: { : 1.0 : 1.0 }

While MongoDB supports an option to drop duplicates, dropDups, during index builds, this option forces the creation of a unique index by way of deleting data. If you use the dropDups option, MongoDB will create an index on the first occurrence of a value for a given key and then  delete all subsequent values. While this behavior may be acceptable in some cases, it’s important to be cautious whenever you are deleting data.

Example aggregation query to identify duplicate keys

To avoid blindly deleting data, one of our engineers, Sean, suggests using the aggregation pipeline framework to easily identify documents with duplicate key values.

// Desired unique index: 
// db.collection.ensureIndex({ firstField: 1, secondField: 1 }, { unique: true})

db.collection.aggregate([
  { $group: { 
    _id: { firstField: "$firstField", secondField: "$secondField" }, 
    uniqueIds: { $addToSet: "$_id" },
    count: { $sum: 1 } 
  }}, 
  { $match: { 
    count: { $gt: 1 } 
  }}
])

In the first stage of this example pipeline, we use the $group operator to aggregate documents by the desired index key values and record (in the uniqueIds field) each _id value of the grouped documents. We also count the number of grouped documents.

In the second stage of this example pipeline, we use the $match operator to filter out all documents with a count of 1. The filtered-out documents represent unique index keys.

The remaining documents identify documents in the collection that contain duplicate keys and would prevent the creation of a unique index on { firstField: 1, lastField: 1 }.

Note that the order of operations in the aggregation pipeline matters (i.e., we cannot $match on count before our $group operation creates the count field).

Only one of many aggregation framework uses

MongoDB’s aggregation framework is very flexible and powerful, which allows for many different usage possibilities. This example is only one of many, many use cases – we hope you’re inspired to try it out!