10

My data looks like this:

{

    "foo_list": [
      {
        "id": "98aa4987-d812-4aba-ac20-92d1079f87b2",
        "name": "Foo 1",
        "slug": "foo-1"
      },
      {
        "id": "98aa4987-d812-4aba-ac20-92d1079f87b2",
        "name": "Foo 1",
        "slug": "foo-1"
      },
      {
        "id": "157569ec-abab-4bfb-b732-55e9c8f4a57d",
        "name": "Foo 3",
        "slug": "foo-3"
      }
    ]
}

Where foo_list is a field in a model called Bar. Notice that the first and second objects in the array are complete duplicates.

Aside from the obvious solution of switching to PostgresSQL, what MongoDB query can I run to remove duplicate entries from foo_list?

Similar answers that do not quite cut it:

These questions answer the question if the array had bare strings in it. However in my situation the array is filled with objects.

I hope it is clear that I am not interested querying the database; I want the duplicates to be gone from the database forever.

1 Answer 1

17

Purely from an aggregation framework point of view there are a few approaches to this.

You can either just apply $setUnion in modern releases:

 db.collection.aggregate([
     { "$project": { 
         "foo_list": { "$setUnion": [ "$foo_list", "$foo_list" ] }
     }}
 ])

Or more traditionally with $unwind and $addToSet:

db.collection.aggregate([
    { "$unwind": "$foo_list" },
    { "$group": {
        "_id": "$_id",
        "foo_list": { "$addToSet": "$foo_list" }
    }}
])

Or if you were just interested in the duplicates only then by general grouping:

db.collection.aggregate([
    { "$unwind": "$foo_list" },
    { "$group": {
        "_id": {
            "_id": "$_id",
            "foo_list": "$foo_list"
        },
        "count": { "$sum": 1 }
    }},
    { "$match": { "count": { "$ne": 1 } } },
    { "$group": {
        "_id": "$_id._id",
        "foo_list": { "$push": "$_id.foo_list" }
    }}
])    

The last form could be useful to you if you actually want to "remove" the duplicates from your data with another update statement as it identifies the elements which are duplicates.

So in that last form the returned result from your sample data identifies the duplicate:

{
    "_id" : ObjectId("53f5f7314ffa9b02cf01c076"),
    "foo_list" : [
            {
                    "id" : "98aa4987-d812-4aba-ac20-92d1079f87b2",
                    "name" : "Foo 1",
                    "slug" : "foo-1"
            }
    ]
}

Where results are returned from your collection per document that contains duplicate entries in the array and which entries are duplicated. This is the information you need to update, and you loop the results as you need to specify the update information from the results in order to remove duplicates.

This is actually done with two update statements per document, as a simple $pull operation would remove "both" items, which is not what you want:

var cursor = db.collection.aggregate([
    { "$unwind": "$foo_list" },
    { "$group": {
        "_id": {
            "_id": "$_id",
            "foo_list": "$foo_list"
        },
        "count": { "$sum": 1 }
    }},
    { "$match": { "count": { "$ne": 1 } } },
    { "$group": {
        "_id": "$_id._id",
        "foo_list": { "$push": "$_id.foo_list" }
    }}
])    

var batch = db.collection.initializeOrderedBulkOp();
var count = 0;

cursor.forEach(function(doc) {
    doc.foo_list.forEach(function(dup) {
        batch.find({ "_id": doc._id, "foo_list": { "$elemMatch": dup } }).updateOne({
            "$unset": { "foo_list.$": "" }
        });
        batch.find({ "_id": doc._id }).updateOne({ 
            "$pull": { "foo_list": null }
        });
    });
    
    count++;
    if ( count % 500 == 0 ) {
        batch.execute();
        batch = db.collection.initializeOrderedBulkOp();
    }
});

if ( count % 500 != 0 ) {
    batch.execute();
}

That's the modern MongoDB 2.6 and above way to do it, with a cursor result from aggregation and Bulk operations for updates. But the principles remain the same:

  1. Identify the duplicates in documents

  2. Loop the results to issue the updates to the affected documents

  3. Use $unset with the positional $ operator to set the "first" matched array element to null

  4. Use $pull to remove the null entry from the array

So after processing the above operations your sample now looks like this:

{
    "_id" : ObjectId("53f5f7314ffa9b02cf01c076"),
    "foo_list" : [
            {
                    "id" : "98aa4987-d812-4aba-ac20-92d1079f87b2",
                    "name" : "Foo 1",
                    "slug" : "foo-1"
            },
            {
                    "id" : "157569ec-abab-4bfb-b732-55e9c8f4a57d",
                    "name" : "Foo 3",
                    "slug" : "foo-3"
            }
    ]
}

The duplicate is removed with the "duplicated" item still intact. That is how you process to identify and remove the duplicate data from your collection.

Sign up to request clarification or add additional context in comments.

12 Comments

The second code listing is identical to both of the answers that I gave as examples of answers that do not work, because this is an array of objects, not an array of strings.
@andrewrk MongoDB does not care. It just treats them as "things". Of course the code as shown is tested and works. I always ( unless running off somewhere, but not the case here ) test before submitting a response.
@andrewrk Perhaps if you think this does not work or have otherwise tried where it does not, then your objects are not in fact real duplicates as you have presented them. If only one thing such as "id" is "duplicated" then the data is not a real "set" and you have to handle the "de-duplicatation" differently. But this is not how you have presented your question.
It looks like you're saying that the second listing does not actually modify the database but just returns query results without duplicates. Strange, because the other questions I linked claim that the second code listing will actually save the removals to the database. I will try the third code listing and see what happens.
The third code listing is the closest thing to a helpful answer, because my goal is not to do a query but to actually modify the database and make the duplicates go away forever. I tried it and I get no results from the query. And I do not mean that the query does not remove duplicates; I mean that it literally has no results returned.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.