MongoDB: Removing duplicate document based on ObjectId?

Question

This is really an open question. I am sorry if this goes little vague but I am trying to collect thoughts from other people since I am very new to Mongo

Situation

• I realized that my collection has multiple duplicate documents (based on name key)
• These documents may be same or might got changed during the subsequent dumps from file(we want to keep later changes)
• Since there is no insert date, it will be hard to tell looking at document which one is latest (bad schema design)

Wanted

• To remove the documents which were inserted earlier
• I read that each document in collection is assigned an ObjectId(here) that makes document unique

Question

• Is it possible to know which document is inserted earlier based on ObjectId and remove it using Map Reduce?
• Any other thoughts and advices?

Answer 1

I'm bored this evening, so here we go.

Step 1. Let's prepare our test data.

> db.users.insert({name: 'John', other_field: Math.random()})
> db.users.insert({name: 'Bob', other_field: Math.random()})
> db.users.insert({name: 'Mary', other_field: Math.random()})
> db.users.insert({name: 'John', other_field: Math.random()})
> db.users.insert({name: 'Jeff', other_field: Math.random()})
> db.users.insert({name: 'Ivan', other_field: Math.random()})
> db.users.insert({name: 'Mary', other_field: Math.random()})
> db.users.find()
{
    "_id" : ObjectId("501976e9bee9b253265bba8b"),
    "name" : "John",
    "other_field" : 0.9884713875252772
}
{
    "_id" : ObjectId("501976e9bee9b253265bba8c"),
    "name" : "Bob",
    "other_field" : 0.048004131996396415
}
{
    "_id" : ObjectId("501976e9bee9b253265bba8d"),
    "name" : "Mary",
    "other_field" : 0.20415803582615222
}
{
    "_id" : ObjectId("501976e9bee9b253265bba8e"),
    "name" : "John",
    "other_field" : 0.5514446987265585
}
{
    "_id" : ObjectId("501976e9bee9b253265bba8f"),
    "name" : "Jeff",
    "other_field" : 0.8685077449753242
}
{
    "_id" : ObjectId("501976e9bee9b253265bba90"),
    "name" : "Ivan",
    "other_field" : 0.2842514340422925
}
{
    "_id" : ObjectId("501976eabee9b253265bba91"),
    "name" : "Mary",
    "other_field" : 0.984048520281136
}

Step 2. The map-reduce

var map = function() {
  emit(this.name, this);
};

var reduce = function(name, vals) {
  var last_obj = null;
  vals.forEach(function(v) {
    if(!last_obj || v._id > last_obj._id) {
      last_obj = v;
    }
  });
  return last_obj;
};

db.users.mapReduce(map, reduce, {out: 'temp_coll'})

It basically groups all documents by name and then selects the one with the largest _id.

Step 3. Do something with unique data.

> db.temp_coll.find()
{
    "_id" : "Bob",
    "value" : {
        "_id" : ObjectId("501976e9bee9b253265bba8c"),
        "name" : "Bob",
        "other_field" : 0.048004131996396415
    }
}
{
    "_id" : "Ivan",
    "value" : {
        "_id" : ObjectId("501976e9bee9b253265bba90"),
        "name" : "Ivan",
        "other_field" : 0.2842514340422925
    }
}
{
    "_id" : "Jeff",
    "value" : {
        "_id" : ObjectId("501976e9bee9b253265bba8f"),
        "name" : "Jeff",
        "other_field" : 0.8685077449753242
    }
}
{
    "_id" : "John",
    "value" : {
        "_id" : ObjectId("501976e9bee9b253265bba8e"),
        "name" : "John",
        "other_field" : 0.5514446987265585
    }
}
{
    "_id" : "Mary",
    "value" : {
        "_id" : ObjectId("501976eabee9b253265bba91"),
        "name" : "Mary",
        "other_field" : 0.984048520281136
    }
}

For example, drop the original collection, iterate this one and insert values into new collection. Don't forget to drop the temp collection when you're done.

Important

I didn't bother with extraction of a timestamp from objectid, because I assumed that you run your import jobs not twice a second (not even every second, maybe).

Answer 2

Ok since object id uses timestamp as it's leading four bytes you can do this with a bit of math.

Thankfully the mongo shell has a way to get the timestamp from an object id by you will need to do some more javascript to first query your documents with the same name then store them in a temp variable (if using the command line) or in a temp table (if using drivers) and parse each individual id's using the timestamp getter that's shown in the link below.

http://www.mongodb.org/display/DOCS/Optimizing+Object+IDs#OptimizingObjectIDs-Extractinsertiontimesfromidratherthanhavingaseparatetimestampfield.

Remember that object id's are only accurate to the second so this still doesn't help in rapid insertion mode.

But either way what you are asking for is doable either in a map reduce function or in the way shown above which does it through the command line.

Give that a shot and if you get stuck let me know. If i know your collection structure i can probably whip up something real quick but only after you bang your head on it a couple of times :)