How to test whether unique index can be created in MongoDB?

I want to add a new unique compound index to our production system. Before doing the change, I would like to check whether the field combination is unique, i.e. will the index creation succeed or fail. (The combination should be unique, but there's always the chance something has gone wrong earlier.)

How can I check whether creating a unique index would succeed, without actually doing the change?

You can use an aggregation query to check for duplicates before trying to create a unique index.

For example, if you want to create a unique index on fields a and b:

-
Insert some test data:

db.mycollection.insert([
    {a: 123, b: 456},
    {a: 123},
    {a: 123, b: 456},
    {c: 789 },
    {d: 321 }
])

-
Find documents with duplicate values:

db.mycollection.aggregate([

    // Group by the field combination you plan to add a unique index on
    { $group: {
        _id: { a: "$a", b: "$b"},
        count: { $sum: 1 }
    }},

    // Find any groups which are not unique (more than 1 matching document)
    { $match: {
        count: { "$gt": 1}
    }}
])

-
Aggregated output for the sample data above would indicate that there are two documents missing the expected fields a and b, and two documents with duplicate values of a and b:

{ "_id" : {  }, "count" : 2 }
{ "_id" : { "a" : 123, "b" : 456 }, "count" : 2 }

In this example I intentionally included some documents with expected fields missing, since those will be indexed as null values if you create a unique index. By default the unique requirement still applies even if one or more fields in the unique index is missing for a given document, because all documents in the collection are added to the index.

If you only want the field combination to be unique if both fields are present (or using some other criteria) you can create a Partial Index with Unique Constraint.

StackExchange Database Administrators Q#220793, answer score: 4