Change a unique index to a sparse unique index in MongoDB?

To change a unique index to a sparse unique index in MongoDB, you must drop the existing index and recreate it with the sparse: true option. MongoDB doesn't support modifying index properties directly.

Syntax

// Drop existing unique index
db.collection.dropIndex("indexName");

// Create sparse unique index
db.collection.createIndex({"field": 1}, {unique: true, sparse: true});

Example: Converting Unique to Sparse Unique Index

Step 1: Create Initial Unique Index

db.demo229.createIndex({"ClientName": 1}, {unique: true});

{
    "createdCollectionAutomatically": true,
    "numIndexesBefore": 1,
    "numIndexesAfter": 2,
    "ok": 1
}

Step 2: Verify Current Index

db.demo229.getIndexes();

[
    {
        "v": 2,
        "key": {
            "_id": 1
        },
        "name": "_id_",
        "ns": "test.demo229"
    },
    {
        "v": 2,
        "unique": true,
        "key": {
            "ClientName": 1
        },
        "name": "ClientName_1",
        "ns": "test.demo229"
    }
]

Step 3: Drop and Recreate as Sparse Unique

db.demo229.dropIndex("ClientName_1");
db.demo229.createIndex({"ClientName": 1}, {unique: true, sparse: true});

{ "nIndexesWas": 2, "ok": 1 }
{
    "createdCollectionAutomatically": false,
    "numIndexesBefore": 1,
    "numIndexesAfter": 2,
    "ok": 1
}

Step 4: Verify Sparse Unique Index

db.demo229.getIndexes();

[
    {
        "v": 2,
        "key": {
            "_id": 1
        },
        "name": "_id_",
        "ns": "test.demo229"
    },
    {
        "v": 2,
        "unique": true,
        "key": {
            "ClientName": 1
        },
        "name": "ClientName_1",
        "ns": "test.demo229",
        "sparse": true
    }
]

Key Points

• Sparse unique indexes ignore documents where the indexed field is null or missing
• Multiple documents can have null values in a sparse unique field
• Use createIndex() instead of deprecated ensureIndex()

Conclusion

Converting a unique index to sparse unique requires dropping the original index and recreating it with sparse: true. The sparse property allows null values while maintaining uniqueness for non-null entries.