MongoDB: Ensure that only one document can have a field set to true

Question

I have a mongo collection with multiple configurations. However, at any one time, only one of these can be "active". I do this by setting an active tag to true.

CylonConfigurations = new Mongo.Collection('cylon_configurations');

Meteor.startup(function () {
  if (Meteor.isServer) {
    CylonConfigurations.upsert({
      host: '192.168.1.4',
      port: 23
    }, {
      $setOnInsert: {
        host: '192.168.1.4',
        port: 23,
        active: true
      }
    }, function (err, s) {
      if (!err) {
        const conn = CylonConfigurations.findOne({ active: true });
        Cylon.connect(conn.port, conn.host);
      }
    });
  }
});

However, the problem I am facing is the case that multiple items within this collection can have the boolean of active set to true, theoretically.

In MongoDB, is there any way to prevent this? Is there any way to ensure that only one document in a collection has a flag set to true, and all the others have it set to false?

Behindhand

You have to look at this in context, especially considering upserts. If you want to "upsert" a new host and set it to active, there is no possible way for that single statement to modify any other document. By definition it is not looking at any other document and the conditions "must not match" in order for an "upsert" to occur. The only safe way to set one of your documents to true and only one, is to set "everything" to false and then one to true. In the case of "upserts", both of those actions would take place "after" the upsert has occurred. – Wisp

Answer 1

You can add an unique sparse index on your active flag after removing flag values that are false. This will ensure only one document can have the active flag set to true (or false).

db.CylonConfigurations.createIndex( { "active": 1 }, { sparse: true,unique: true } )

Alternatively you could create a new collection that just stores the ObjectId of the currently active configuration, for example in a field called ActiveConfigurationId. In this model, you can just update this field to change the active configuration.

Foxglove

It would throw duplicate key errors if you tried to do anything else, and with a boolean value ( or omitted in fact ) there can only be a maximum "two" documents in a collection. Not really a great way of looking at the problem and not really what the OP appears to be asking, which is out of a number of "hosts" how to ensure that there is only one "active" at a time. That generally means more than "two". – Wisp

A parse index can handle missing fields, you can have as many records as you want, although, you can only have at most two records with the active flag set. – Foxglove

The "problem" if you read it is "how to update only one thing to active at a time". So even with a "sparse" index, you could not set one document to an "active" status whilst removing from all other possible documents at the same time. So a sparse index really adds nothing that "no-index" or a "non-unique" index does not already not solve. This is about update patterns of values and not indexes. Understand now. – Wisp

Answer 2

We can use partialFilterExpression where we can filter only Active state document and set unique true which will ensure only one document is Active at any given moment. This will help over sparse type solution limitations.

db.CylonConfigurations.createIndex({"status":1}, {unique:true, partialFilterExpression: {status : "Active"}})

Peen