MongoDB / mongoose: unique if not null

Question

I was wondering if there is a way to force a unique collection entry , but only if the entry is not null . f Example circuit:

var UsersSchema = new Schema({ name : {type: String, trim: true, index: true, required: true}, email : {type: String, trim: true, index: true, unique: true} }); 

An “email” is not required in this case, but if the “email” is saved, I want to make sure that this entry is unique (at the database level).

Empty entries seem to get a null value, so each entry does not crash email with a "unique" option (if there is another user without email).

Now I solve it at the application level, but would like to keep this db request.

THX

Answer 1

Starting with MongoDB v1.8 +, you can get the desired behavior to provide unique values, but allow multiple documents without a field by setting the sparse parameter to true when defining the index. How in:

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

Or in the shell:

 db.users.ensureIndex({email: 1}, {unique: true, sparse: true}); 

Please note that the unique, sparse index still does not allow multiple documents with an email field with a value of null , only a few documents without an email field.

See http://docs.mongodb.org/manual/core/index-sparse/

Answer 2

TL; dr

Yes, it is possible to have several documents with a field equal to null or not defined, while using unique "actual" values.

requirements :

• MongoDB v3. 2+.
• Knowing your specific value types in advance (for example, always string or object when not null ).

If you are not interested in the details, feel free to skip to the implementation section.

long version

In addition to @Nolan's answer, starting with MongoDB v3.2, you can use a partial unique index with a filter expression.

A partial filter expression has limitations. This may include only the following:

• equality expressions (i.e. field: value or use of the $eq operator),
• $exists: true expression,
• expressions $gt , $gte , $lt , $lte ,
• $type expressions,
• $and top-level operator

This means that the trivial expression {"yourField"{$ne: null}} cannot be used.

However, assuming your field always uses the same type , you can use the expression $type .

 { field: { $type: <BSON type number> | <String alias> } } 

MongoDB v3.6 adds support for specifying several possible types that can be passed as an array:

 { field: { $type: [ <BSON type1> , <BSON type2>, ... ] } } 

This means that it allows a value to have any of a variety of types when it is not null .

Therefore, if we want to allow the email field in the example below to accept either string or, say, binary data , binary data values, the corresponding $type expression will look like:

 {email: {$type: ["string", "binData"]}} 

implementation

mongoose

You can specify this in the mongoose pattern:

 const UsersSchema = new Schema({ name: {type: String, trim: true, index: true, required: true}, email: { type: String, trim: true, index: { unique: true, partialFilterExpression: {email: {$type: "string"}} } } }); 

or directly add it to the collection (which uses the node.js native driver):

 User.collection.createIndex("email", { unique: true, partialFilterExpression: { "email": { $type: "string" } } }); 

native driver mongodb

using collection.createIndex

 db.collection('users').createIndex({ "email": 1 }, { unique: true, partialFilterExpression: { "email": { $type: "string" } } }, function (err, results) { // ... } ); 

Mongodb shell

using db.collection.createIndex :

 db.users.createIndex({ "email": 1 }, { unique: true, partialFilterExpression: { "email": {$type: "string"} } }) 

This will allow you to insert multiple entries with a null email address or no email field at all, but not with the same email string.

Answer 3

A simple update for anyone exploring this topic.

The selected answer will work, but you may want to use partial indexes instead.

Changed in version 3.2: Starting with MongoDB 3.2, MongoDB provides the ability to create partial indexes. Partial indexes offer a superset of the functionality of sparse indexes. If you are using MongoDB 3.2 or later, partial indexes should be preferred over more sparse indexes.

More doco on partial indexes: https://docs.mongodb.com/manual/core/index-partial/

Answer 4

In fact, only the first document in which the "email" field does not exist will be successfully saved. Subsequent savings where there is no “email” will not work when an error is generated (see code snippet below). For this reason, check out MongoDB's official documentation regarding unique indexes and missing keys here at http://www.mongodb.org/display/DOCS/Indexes#Indexes-UniqueIndexes .

  // NOTE: Code to executed in mongo console. db.things.ensureIndex({firstname: 1}, {unique: true}); db.things.save({lastname: "Smith"}); // Next operation will fail because of the unique index on firstname. db.things.save({lastname: "Jones"}); 

By definition, a unique index can only store one value only once. If you consider null as one of these values, you can insert it only once! You are right in your approach by providing and testing it at the application level. Here's how to do it.

You may also like to read http://www.mongodb.org/display/DOCS/Querying+and+nulls