CODEWITHSUNDEEP
mongodb
gl00ten
gl00ten

Reputation: 1121

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

I know there's a way to do db.collection.getIndexes() Which will list all the indexes defined for a collection. Is there a way to copy and create those index definitions to another collection?

There's a lot of them and I don't want to do them one by one.

regarding the duplicated question comment: I do not wish to copy a collection. I wish to export indexes in a format that I can apply to another collection.

Upvotes: 14

Views: 20912

Answers (5)

Manoj Selvin
Manoj Selvin

Reputation: 2383

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/

Upvotes: 14

sujith s
sujith s

Reputation: 914

Copy all indexes from one database another database

 use firstDbName;
 var indexKeyArray = [];
 db.getCollectionNames().forEach(function(collection) {
 var indexKeys = db[collection].getIndexKeys();
 var base = {};
 base["name"] = collection;
 base["indices"] = indexKeys
 indexKeyArray.push(base);
});
#printjson(indexKeyArray);
use destinationDbName;
indexKeyArray.forEach(function(data) {
  db[data.name].createIndexes(data.indices);
});

Upvotes: 0

Pierce Mason
Pierce Mason

Reputation: 3

Rocky Li's answer was helpful but did not create the index options properly at the time of writing (It gathered the option values but not the keys). The following modification worked for me:

var indexes = db.user.getIndexes();

indexes.forEach(function(index){
    delete index.v;
    delete index.ns;
    var key = index.key;
    delete index.key

    // uncomment if you want to ensure creation is in background
    //if(!('background' in index))
        //index['background'] = true;

   db.user.createIndex(key, index);
});

Upvotes: 0

mao andy
mao andy

Reputation: 81

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);

   }
});

Upvotes: 4

Rocky Li
Rocky Li

Reputation: 646

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

Upvotes: 27

Related Questions