Remove Duplicates from MongoDB

by

The “dropDups” syntax for index creation has been “deprecated” as of MongoDB 2.6 and removed in MongoDB 3.0. It is not a very good idea in most cases to use this as the “removal” is arbitrary and any “duplicate” could be removed. Which means what gets “removed” may not be what you really want removed.

Anyhow, you are running into an “index length” error since the value of the index key here would be longer that is allowed. Generally speaking, you are not “meant” to index 43 fields in any normal application.

If you want to remove the “duplicates” from a collection then your best bet is to run an aggregation query to determine which documents contain “duplicate” data and then cycle through that list removing “all but one” of the already “unique” _id values from the target collection. This can be done with “Bulk” operations for maximum efficiency.

NOTE: I do find it hard to believe that your documents actually contain 43 “unique” fields. It is likely that “all you need” is to simply identify only those fields that make the document “unique” and then follow the process as outlined below:

var bulk = db.testkdd.initializeOrderedBulkOp(),
    count = 0;

// List "all" fields that make a document "unique" in the `_id`
// I am only listing some for example purposes to follow
db.testkdd.aggregate([
    { "$group": {
        "_id": {
           "duration" : "$duration",
          "protocol_type": "$protocol_type", 
          "service": "$service",
          "flag": "$flag"
        },
        "ids": { "$push": "$_id" },
        "count": { "$sum": 1 }
    }},
    { "$match": { "count": { "$gt": 1 } } }
],{ "allowDiskUse": true}).forEach(function(doc) {
    doc.ids.shift();     // remove first match
    bulk.find({ "_id": { "$in": doc.ids } }).remove();  // removes all $in list
    count++;

    // Execute 1 in 1000 and re-init
    if ( count % 1000 == 0 ) {
       bulk.execute();
       bulk = db.testkdd.initializeOrderedBulkOp();
    }
});

if ( count % 1000 != 0 ) 
    bulk.execute();

If you have a MongoDB version “lower” than 2.6 and don’t have bulk operations then you can try with standard .remove() inside the loop as well. Also noting that .aggregate() will not return a cursor here and the looping must change to:

db.testkdd.aggregate([
   // pipeline as above
]).result.forEach(function(doc) {
    doc.ids.shift();  
    db.testkdd.remove({ "_id": { "$in": doc.ids } });
});

But do make sure to look at your documents closely and only include “just” the “unique” fields you expect to be part of the grouping _id. Otherwise you end up removing nothing at all, since there are no duplicates there.

More Related Contents:

  1. Does MongoDB’s $in clause guarantee order
  2. Check if every element in array matches condition
  3. MongoDB aggregation comparison: group(), $group and MapReduce
  4. Retrieve only the queried element in an object array in MongoDB collection
  5. Update MongoDB field using value of another field
  6. $lookup on ObjectId’s in an array
  7. MongoDB: Combine data from multiple collections into one..how?
  8. Return only matched sub-document elements within a nested array
  9. Converting string to date in mongodb
  10. MongoDB nested lookup with 3 levels
  11. MongoDB Full and Partial Text Search
  12. Matching ObjectId to String for $graphLookup
  13. Mongodb Join on _id field from String to ObjectId
  14. Mongodb aggregation lookup with conditions
  15. How to get back the Original document back after aggregation
  16. Mongo sort on a calculated condition
  17. Find all duplicate documents in a MongoDB collection by a key field
  18. MongoDB lookup when foreign field is an array of objects
  19. MongoDB: Calculate dwell time between every status value change
  20. MongoDB: how to parse date in 3.6 mongoDb version?
  21. Search on multiple collections in MongoDB
  22. Using stored JavaScript functions in the Aggregation pipeline, MapReduce or runCommand
  23. MongoDB – The argument to $size must be an Array, but was of type: EOO / missing
  24. Using $slice operator to get last element of array
  25. How to access overall document count during arithmetic aggregation expression
  26. count array occurrences across all documents with mongo
  27. $project in $lookup mongodb
  28. Return actual type of a field in MongoDB
  29. Mongodb, aggregate query with $lookup
  30. Return the last “true” value for each group

Leave a Comment