mongoDB/mongoose: unique if not null
Mongodbnode.jsMongooseMongodb Problem Overview
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
Mongodb Solutions
Solution 1 - Mongodb
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.
Solution 2 - Mongodb
#tl;dr
Yes, it is possible to have multiple documents with a field set to null
or not defined, while enforcing unique "actual" values.
requirements:
- MongoDB v3.2+.
- Knowing your concrete value type(s) in advance (e.g, always a
string
orobject
when notnull
).
If you're not interested in the details, feel free to skip to the implementation
section.
#longer version
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"]}}
implementation
mongoose
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"
}
}
});
native mongodb driver
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 inserting multiple records with a null
email, or without an email field at all, but not with the same email string.
Solution 3 - Mongodb
Just a quick update to those researching this topic.
The selected answer will work, but you might want to consider using partial indexes instead.
> Changed in version 3.2: Starting in MongoDB 3.2, MongoDB provides the > option 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 sparse indexes.
More doco on partial indexes: https://docs.mongodb.com/manual/core/index-partial/
Solution 4 - Mongodb
Actually, only first document where "email" as field does not exist will get save successfully. Subsequent saves where "email" is not present will fail while giving error ( see code snippet below). For the reason look at MongoDB official documentation with respect to 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 unique index can only allow one value to be stored only once. If you consider null as one such value it can only be inserted once! You are correct in your approach by ensuring and validating it at application level. That is how it can be done.
You may also like to read this http://www.mongodb.org/display/DOCS/Querying+and+nulls