NoSQL Zone is brought to you in partnership with:

Developer Advocate at MongoLab. Previously interned at VMware and NetApp. UCSD CSE 2013 Alum. Chris is a DZone MVB and is not an employee of DZone and has posted 18 posts at DZone. You can read more from them at their website. View Full User Profile

Finding Duplicate Keys with MongoDB’s Aggregation Framework

03.24.2014
| 4384 views |
  • submit to reddit

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!


Published at DZone with permission of Chris Chang, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)