Mongoose unique if not null and if state

Question

I have a unique index like this

    code: {
        type: String,
        index: {
            unique: true,
            partialFilterExpression: {
                code: { $type: 'string' }
            }
        },
        default: null
    },
    state: { type: Number, default: 0 },

but When the state is 2 (archived) I want to keep the code, but it should be able to reuse the code, so it cannot be unique if state is 2. Is there any away that I could accomplish this?

Answer 1

This is possible, though it's through a work around documented here https://jira.mongodb.org/browse/SERVER-25023.

In MongoDB 4.7 you will be able to apply different index options to the same field but for now you can add a non-existent field to separate the two indexes.

Here's an example using the work around.

(async () => {
  const ItemSchema = mongoose.Schema({
    code: {
      type: String,
      default: null
    },
    state: {
      type: Number,
      default: 0,
    },
  });

  // Define a unique index for active items
  ItemSchema.index({code: 1}, {
    name: 'code_1_unique',
    partialFilterExpression: {
      $and: [
        {code: {$type: 'string'}},
        {state: {$eq: 0}}
      ]
    },
    unique: true
  })

  // Defined a non-unique index for non-active items
  ItemSchema.index({code: 1, nonExistantField: 1}, {
    name: 'code_1_nonunique',
    partialFilterExpression: {
      $and: [
        {code: {$type: 'string'}},
        {state: {$eq: 2}}
      ]
    },
  })

  const Item = mongoose.model('Item', ItemSchema)

  await mongoose.connect('mongodb://localhost:27017/so-unique-compound-indexes')
  
  // Drop the collection for test to run correctly
  await Item.deleteMany({})

  // Successfully create an item
  console.log('\nCreating a unique item')
  const itemA = await Item.create({code: 'abc'});


  // Throws error when trying to create with the same code
  await Item.create({code: 'abc'})
    .catch(err => {console.log('\nThrowing a duplicate error when creating with the same code')})


  // Change the active code
  console.log('\nChanging item state to 2')
  itemA.state = 2; 
  await itemA.save();


  // Successfully created a new doc with sama code
  await Item.create({code: 'abc'})
    .then(() => console.log('\nSuccessfully created a new doc with sama code'))
    .catch(() => console.log('\nThrowing a duplicate error'));
  

  // Throws error when trying to create with the same code 
  Item.create({code: 'abc'})
  .catch(err => {console.log('\nThrowing a duplicate error when creating with the same code again')})
})();