Is there a way to create or update a MongoDB index?

Question

According to the documentation on the createIndexes command:

If you create an index with one set of options and then issue createIndexes with the same index fields but different options, MongoDB will not change the options nor rebuild the index.

The solution is to drop the index and create it from scratch, but that's costly.

Is there a way to create an index when there isn't one, do nothing if there is an index with the same options, and replace the index if the options have changed?

This question was originally raised by Phil Barresi here but has since been deleted.

score 1 · Accepted Answer · answered Jul 20 '14 at 12:55

Looking at the driver I've implemented a CreateOrUpdateIndex extension method that compares the raw index documents and if the index options have changed the index is replaced (as long as the index name stays the same):

public static WriteConcernResult CreateOrUpdateIndex(
    this MongoCollection mongoCollection,
    IMongoIndexKeys keys,
    IMongoIndexOptions options = null)
{
    if (mongoCollection.IndexExists(keys))
    {
        var indexDocument = mongoCollection.GenerateIndexDocument(keys, options);
        if (!mongoCollection.GetIndexes().RawDocuments.Any(indexDocument.Equals))
        {
            mongoCollection.DropIndex(keys);
        }
    }

    return mongoCollection.CreateIndex(keys, options);
}

Generating the raw index document:

public static BsonDocument GenerateIndexDocument(this MongoCollection mongoCollection, IMongoIndexKeys keys, IMongoIndexOptions options)
{
    var optionsDocument = options.ToBsonDocument();
    var keysDocument = keys.ToBsonDocument();
    var indexDocument = new BsonDocument
    {
        { "ns", mongoCollection.FullName },
        { "name", GenerateIndexName(keysDocument, optionsDocument) },
        { "key", keysDocument }
    };
    if (optionsDocument != null)
    {
        indexDocument.Merge(optionsDocument);
    }

    return indexDocument;
}

public static string GenerateIndexName(IEnumerable<BsonElement> keys, BsonDocument options)
{
    const string name = "name";
    if (options != null && options.Contains(name)) return options[name].AsString;

    return string.Join("_", keys.Select(element =>
    {
        var value = "x";
        switch (element.Value.BsonType)
        {
            case BsonType.Int32: value = ((BsonInt32)element.Value).Value.ToString(); break;
            case BsonType.Int64: value = ((BsonInt64)element.Value).Value.ToString(); break;
            case BsonType.Double: value = ((BsonDouble)element.Value).Value.ToString(); break;
            case BsonType.String: value = ((BsonString)element.Value).Value; break;
        }
        return string.Format("{0}_{1}", element.Name, value.Replace(' ', '_'));
    }));
}

My c# is rusty however, shouldn't createIndexes actually be a no-op if the index already exists? — Sammaye, Jul 20 '14 at 18:34
@Sammaye It is, it will not update the index. That means that when you update the options you need to specifically remove the old index and create the new one, which `CreateOrUpdateIndex` automates — i3arnon, Jul 20 '14 at 20:23
Oh ok I see now, you only detect by keys and then judge if the options between the two keyed indexes match, ok kool — Sammaye, Jul 20 '14 at 20:37
@Sammaye yeah, that's actually how `IndexExists` is implemented internally, regarding the index keys... — i3arnon, Jul 20 '14 at 20:39
This solution doesn't include the "v" field, so the comparison always fails. Is there an updated version that can ignore that field or set it to the right value (with future compatibility)? — Nathan, Apr 15 '15 at 16:55
@Nathan do you mean for the old 1.x driver? Or the new v2.0 one? — i3arnon, Apr 15 '15 at 18:01
I'm using MongoDB 2.6.6 and MongoDB.Driver 1.9.2. The "v" fields is from the database. It returns a "v"=1 Element that the GenerateIndexDocument doesn't include. Also, the BsonElements are in a different order, so if the "v" is removed, the indexDocument.Equals still fails. — Nathan, Apr 15 '15 at 18:06

score 1 · Answer 2 · answered Oct 28 '15 at 10:56

I had the same problem, and the most robust way I've found to overcome this problem is trapping exception and explicitly specify Index name. If you do not specify index name the driver will generate a name using keys, and even if you can determine the name, it is not really robust because it could change with different version of driver.

            try
            {
                _collection.CreateIndex(keys, options);
            }
            catch (MongoWriteConcernException ex)
            {
                //probably index exists with different options, lets check if name is specified
                var optionsDoc = options.ToBsonDocument();
                if (!optionsDoc.Names.Contains("name"))
                    throw;

                var indexName = optionsDoc["name"].AsString;
                _collection.DropIndexByName(indexName);
                _collection.CreateIndex(keys, options);
            }

I know that code that uses exception for normal flow of operation is ugly, and I know also that this code should check for exact reason why a WriteConcernException is raised, but it actually works.

If the options does not contains a name attribute I simply rethrow the exception, but if a name is specified I try to delete the index and then recreate the index again.

If the error is due to different reason (not different options / fields) the second CreateIndex will probably throw again and then it is duty of caller code to understand what really happened.

shirbr510 · Answer 3 · 2020-06-03T13:21:32.233

I've had the same problem while using the mongo c# driver (v2.2.3) and saw Alkampfer's answer.

in version 2.2.3, indexes are managed through the collection's Indexes property.

I've written an extension method for the indexes property based on Alkampfer's answer as follows:

public static async Task AddOrUpdateAsync<TDocument>(this IMongoIndexManager<TDocument> indexes, CreateIndexOptions options, IndexKeysDefinition<TDocument> keys = null, CancellationToken cancellationToken = default(CancellationToken))
        {
            if (options == null)
            {
                throw new ArgumentNullException(nameof(options));
            }
            if (keys == null)
            {
                keys = Builders<TDocument>.IndexKeys.Ascending(options.Name);
            }
            try
            {
                await indexes.CreateOneAsync(keys, options, cancellationToken).ConfigureAwait(false);
            }
            catch (MongoCommandException e)
            {
                await indexes.DropOneAsync(options.Name, cancellationToken).ConfigureAwait(false);
                await AddOrUpdateAsync(indexes, options, keys, cancellationToken).ConfigureAwait(false);
            }
        }

Although it's a little dirty (catching exceptions and retrying is not best practice) it's the simplest solution to the issue

If you are afraid for the code's Performance, you usually use the dirty part once per change to indexes(which as I recall, doesn't happen that often).

If you want there's an alternative of iterating over the collection's indexes, but it's a rather messy process (moveNexts and accessing current position)

score 0 · Answer 4 · answered Nov 04 '20 at 19:22

These days with MongoDB.Driver you should be able to re-create the index this way:

var client = new MongoClient("connection-string-here");
var database = client.GetDatabase("MyDatabaseName");

var index = Builders<MyDocument>.IndexKeys.Ascending(x => x.MyDocRefNumber);
var idexModel = new CreateIndexModel<MyDocument>(index);
database.<MyDocument>()Indexes.CreateOne(indexModel);

Is there a way to create or update a MongoDB index?

4 Answers4

Linked