8

I have created a mongoose model that has an email field. I want it to be unique if a value is provided by a user but I want it to be empty is a user has not provided any value. I have found a good mongodb reference here: https://docs.mongodb.com/manual/core/index-partial/#partial-index-with-unique-constraints that could work but I don't know how to make it work on mongoose

This is how the field looks like right now

email: {
    type: String,
    index: true,
    unique: true
  }

If I leave it the way it is, I cant create multiple documents with an empty/null email field

Navish
  • 99
  • 1
  • 4
  • When you say empty if that means field exists with an empty string then look at my answer, if you don't need that field at all then nothing has to be done as you're not making that field as required !! – whoami - fakeFaceTrueSoul Aug 09 '19 at 16:50
  • Yes the field exists but not required. But when a user provides a value it has to be unique – Navish Aug 09 '19 at 16:57

4 Answers4

17

In the email path level, you can use only:

email: {
  type: String
}

And in the schema level use:

SchemaName.index({ email: 1 }, {
  unique: true,
  partialFilterExpression: {
    'email': { $exists: true, $gt: '' }
  }
});

This way the unique constraint is applied only if email exists and is not an empty string

Liran Mery
  • 171
  • 1
  • 4
  • 1
    This was actually the only way for me to get this functionality working. It was not possible using the answer below for some reason. I did delete the entire database when I tested each time I changed the Schema. – Gennon Mar 23 '22 at 12:50
4

You can have something like :

email: {
    type: String,
    index: {
      unique: true,
      partialFilterExpression: { email: { $type: 'string' } },
    },
    default : null
  }

but do read below link, before you actually implement it, as defaults seems to work only on new document inserts :-

Mongoose v5.6.9 Documentation

whoami - fakeFaceTrueSoul
  • 17,086
  • 6
  • 32
  • 46
  • 1
    I have tried that, it doesn't work. Mongo throws a duplicate key error for both empty strings and null fields if I try to insert more than collection with an empty email field. – Navish Aug 09 '19 at 16:55
  • @Navish : try now !! – whoami - fakeFaceTrueSoul Aug 09 '19 at 16:59
  • 1
    @Navish : what's the issue you're looking at, are you doing inserts or updates, did you try to drop already created index and run this ? – whoami - fakeFaceTrueSoul Aug 09 '19 at 17:07
  • I am seeding some sample data into the db, its an array of six objects some have a value and some don't have a value on the email field. The ones with a value and one of the empty ones are seeded into the database the rest throw this error: E11000 duplicate key error collection – Navish Aug 09 '19 at 17:15
  • yes I run a function that clears the collection before seeding any new data – Navish Aug 09 '19 at 17:16
  • Can you try dropping that collection and run this, if it's ok to do so.. – whoami - fakeFaceTrueSoul Aug 09 '19 at 17:17
  • 1
    Can confirm that dropping the existing collection works. Adding an index to the schema of an existing collection will not add the index to the collection itself. – Bjartskular Jan 15 '21 at 16:55
0

You can use sparse

email: {
  type: String,
  unique: true,
  sparse: true
}

That way if you dont't send the email field at all mongo will not add automatically null value for the field. It will just skip it.

Svetlozar
  • 967
  • 2
  • 10
  • 23
0

Try to cerate TTL index for field "topic" which matches with value "DTC" and "PDM". After running this command, new TTL index will be created with name as "test_index". Result: All documents which are older than 120 seconds with field value "DTC" or "PDM" will be deleted.

db.collection.createIndex( { received: 1 }, { partialFilterExpression: { topic: { $eq: "DTC" }, topic: { $eq: "PDM"} }, expireAfterSeconds: 120, name: "test_index" })

Anuj Saraswat
  • 1
  • 1