1

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?

  • What is the MongoDB version(x,y,z) ? – Md Haidar Ali Khan Oct 23 '18 at 9:11
  • ,what is storage engine? – Md Haidar Ali Khan Oct 23 '18 at 9:11
  • We're running MongoDB 3.4 on WiredTiger, but answers on other/newer versions are welcome as well. – Sampo Oct 23 '18 at 17:57
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.