I was wondering if there is way to force a unique collection entry but only if entry is not null. e Sample schema:
var UsersSchema = new Schema({
name : {type: String, trim: true, index: true, required: true},
email : {type: String, trim: true, index: true, unique: true}
});
'email' in this case is not required but if 'email' is saved I want to make sure that this entry is unique (on a database level).
Empty entries seem to get the value 'null' so every entry wih no email crashes with the 'unique' option (if there is a different user with no email).
Right now I'm solving it on an application level, but would love to save that db query.
thx
As of MongoDB v1.8+ you can get the desired behavior of ensuring unique values but allowing multiple docs without the field by setting the sparse
option to true when defining the index. As 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});
Note that a unique, sparse index still does not allow multiple docs with an email
field with a value of null
, only multiple docs without an email
field.
Yes, it is possible to have multiple documents with a field set to null
or not defined, while enforcing unique "actual" values.
requirements:
string
or object
when not null
).If you're not interested in the details, feel free to skip to the implementation
section.
To supplement @Nolan's answer, starting with MongoDB v3.2 you can use a partial unique index with a filter expression.
The partial filter expression has limitations. It can only include the following:
- equality expressions (i.e. field: value or using the
$eq
operator),$exists: true
expression,$gt
,$gte
,$lt
,$lte
expressions,$type
expressions,$and
operator at the top-level only
This means that the trivial expression {"yourField"{$ne: null}}
cannot be used.
However, assuming that your field always uses the same type, you can use a $type
expression.
{ field: { $type: <BSON type number> | <String alias> } }
MongoDB v3.6 added support for specifying multiple possible types, which can be passed as an array:
{ field: { $type: [ <BSON type1> , <BSON type2>, ... ] } }
which means that it allows the value to be of any of a number of multiple types when not null
.
Therefore, if we want to allow the email
field in the example below to accept either string
or, say, binary data
values, an appropriate $type
expression would be:
{email: {$type: ["string", "binData"]}}
You can specify it in a mongoose schema:
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 native node.js driver):
User.collection.createIndex("email", {
unique: true,
partialFilterExpression: {
"email": {
$type: "string"
}
}
});
using collection.createIndex
db.collection('users').createIndex({
"email": 1
}, {
unique: true,
partialFilterExpression: {
"email": {
$type: "string"
}
}
},
function (err, results) {
// ...
}
);
using db.collection.createIndex
:
db.users.createIndex({
"email": 1
}, {
unique: true,
partialFilterExpression: {
"email": {$type: "string"}
}
})
This will allow inserting multiple records with a null
email, or without an email field at all, but not with the same email string.