isActive boolean that only allows one active item

Tisha_Murvihill · 2024-04-29T23:59:42.472Z

Hello,

I would like an isActive field that only allows one active item, but multiple inActive items. Is there a way to do that with unique indexes or do I have to do that with business logic in the API, or perhaps some other cool trick?

I tried to use partial filters, but I would need the filter {“isActive”: true} to be applied before the unique index and it seems the unique property on the index applies to the whole collection whether or not a partial filter is applied.

Thanks for any help.

Akshat_Gupta3 · 2024-04-30T06:05:46.197Z

Hi Tisha,

Not sure what you mean perhaps sharing some sample documents and expected behaviour would help.
As far as i was able to understand i think what you want to achieve is that if there is let’s suppose a user collection then at a time only one user document can have isActive set to true even if the collection contains multiple documents for the same user. You can achieve this by applying a compound index with both the userId and isActive and mark them as unique.

db.users.createIndex( { "userId": 1, "isActive": 1 }, { unique: true } )

The above solution is based on my current understanding of the problem you gave but maybe I or anyone else in community would be able to help better if more context is given.