MongoDB How to copy index definitions from one collection to another?

For example I have one existing user collection with indexes _id_, name_1, email_1 and website_1

Then I have another collection called usertest, I want to copy indexes from user collection to usertest collection. The following commands works for this scenario:

  1. Copy both index key and index options

    var indexes = db.user.getIndexes();

indexes.forEach(function(index){
    delete index.v;
    delete index.ns;
    var key = index.key;
    delete index.key
    var options = [];
    for (var option in index) {
        options.push(index[option]);
    }
   db.usertest.createIndex(key, options);
});

  2. Copy index key only (batch processing)

    var indexKeys = db.user.getIndexKeys();
db.usertest.createIndexes(indexKeys);

Hope this will be helpful. Here's the doc: createIndexes


Thank you for the answer from Rocky and Bloke which helped me a lot here is the consolidated version as suggested by Bloke. and in PRODUCTION. we would like to make sure the background: true is used to avoid slave halt query when indexes creation replicated.

var indexes = db.user.getIndexes();
// we skipped the __id__ indexes and set the default background: true option
indexes.forEach(function(index){
    if(index.name =='_id_'){
     print("we are skip the _id_ index")
    }else{
    delete index.v;
    delete index.ns;
    var key = index.key;
    delete index.key
    var options = {};
    for (var option in index) {
        options[option] = index[option]
    }
    options['background'] = true;
    printjson(key);
    printjson(options);
    db.usertest.createIndex(key, options);

   }
});

To do this directly in MongoDB do the following,

The following command will generate mongo DB queries for existing indexes of all collections,

db.getCollectionNames().forEach(function(col) {
    var indexes = db[col].getIndexes();
    indexes.forEach(function (c) {
        var fields = '', result = '', options = {};
        for (var i in c) {
            if (i == 'key') {
                fields = c[i];
            } else if (i == 'name' && c[i] == '_id_') {
                return;
            } else if (i != 'name' && i != 'v' && i != 'ns') {
                options[i] = c[i];
            }
        }
        var fields = JSON.stringify(fields);
        var options = JSON.stringify(options);
        if (options == '{}') {
            result = "db." + col + ".createIndex(" + fields + "); ";
        } else {
            result = "db." + col + ".createIndex(" + fields + ", " + options + "); ";
        }
        result = result
            .replace(/{"floatApprox":-1,"top":-1,"bottom":-1}/ig, '-1')
            .replace(/{"floatApprox":(-?\d+)}/ig, '$1')
            .replace(/\{"\$numberLong":"(-?\d+)"\}/ig, '$1');
        print(result);
    });
});

The above command will output something like the following, based on the amount of collection you have

db.User.createIndex({"createdAt":-1}, {"background":true}); 

db.User.createIndex({"updatedAt":-1}, {"background":true}); 

db.Login.createIndex({"loginDate":-1}, {"background":true});

So after executing this, copy the MongoDB queries that are generated above to create the indexes to the new collection, Change the collection name in that then execute it.

For eg: to copy all indexes belonging to the User collection to the UserNew collection, I will rename the query's old collection name to new like the following and execute it, that is it, now you have all the indexes copied to a new collection from the old one.

db.UserNew.createIndex({"createdAt":-1}, {"background":true}); 

db.UserNew.createIndex({"updatedAt":-1}, {"background":true});

Credits: http://aleksandrmaiorov.com/2019/04/29/mongo-how-to-copy-indexes-from-one-database-to-another/

Tags:

Mongodb

Recent Posts