node.js mongoDB/mongoose：如果不为空则唯一

mongoDB/mongoose: unique if not null

提问by ezmilhouse

I was wondering if there is way to force a unique collection entry but only if entry is not null. e Sample schema:

我想知道是否有办法强制唯一的集合条目，但前提是 entry 不是 null。e 示例架构：

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).

空条目似乎获得值 'null'，因此没有电子邮件的每个条目都使用 'unique' 选项崩溃（如果有不同的用户没有电子邮件）。

Right now I'm solving it on an application level, but would love to save that db query.

现在我正在应用程序级别解决它，但很想保存该数据库查询。

thx

谢谢

回答by JohnnyHK

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 sparseoption to true when defining the index. As in:

从 MongoDB v1.8+ 开始，您可以通过sparse在定义索引时将选项设置为 true来获得所需的行为，即确保唯一值但允许没有该字段的多个文档。如：

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 emailfield with a valueof null, only multiple docs withoutan emailfield.

需要注意的是独特的，稀疏索引仍然不允许多个文档与email同一个字段值的null，只有多个文档没有一个email领域。

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

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

回答by MasterAM

tl;dr

tl;博士

Yes, it is possible to have multiple documents with a field set to nullor not defined, while enforcing unique "actual" values.

是的，可以有多个文档的字段设置为null或未定义，同时强制执行唯一的“实际”值。

requirements:

要求：

• MongoDB v3.2+.
• Knowing your concrete value type(s) in advance (e.g, always a stringor objectwhen not null).

• MongoDB v3.2+。
• 提前了解您的具体值类型（例如，始终为 astring或object何时不为null）。

If you're not interested in the details, feel free to skip to the implementationsection.

如果您对细节不感兴趣，请随时跳至该implementation部分。

longer version

更长的版本

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

为了补充@Nolan 的答案，从 MongoDB v3.2 开始，您可以使用带有过滤器表达式的部分唯一索引。

The partial filter expression has limitations. It can only include the following:

部分过滤器表达式有局限性。它只能包括以下内容：

• equality expressions (i.e. field: value or using the $eqoperator),
• $exists: trueexpression,
• $gt, $gte, $lt, $lteexpressions,
• $typeexpressions,
• $andoperator at the top-level only

• 相等表达式（即字段：值或使用$eq运算符），
• $exists: true表达，
• $gt, $gte, $lt,$lte表达式,
• $type表达，
• $and仅在顶层操作符

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

这意味着{"yourField"{$ne: null}}不能使用平凡的表达。

However, assuming that your field always uses the same type, you can use a $typeexpression.

但是，假设您的字段始终使用相同的类型，您可以使用$type表达式。

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

MongoDB v3.6 added support for specifying multiple possible types, which can be passed as an array:

MongoDB v3.6 添加了对指定多种可能类型的支持，这些类型可以作为数组传递：

{ 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.

这意味着当 not 时，它允许值是多种类型中的任何一种null。

Therefore, if we want to allow the emailfield in the example below to accept either stringor, say, binary datavalues, an appropriate $typeexpression would be:

因此，如果我们想让email下面示例中的字段接受任一值string或binary data值，则适当的$type表达式为：

{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):

或直接将其添加到集合中（使用本机 node.js 驱动程序）：

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

native mongodb driver

本地 mongodb 驱动程序

using collection.createIndex

使用 collection.createIndex

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

mongodb shell

mongodb 外壳

using db.collection.createIndex:

使用db.collection.createIndex：

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

This will allow inserting multiple records with a nullemail, or without an email field at all, but not with the same email string.

这将允许使用null电子邮件插入多个记录，或者根本没有电子邮件字段，但不能使用相同的电子邮件字符串。

回答by Nolan Garrido

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.

在 3.2 版更改：从 MongoDB 3.2 开始，MongoDB 提供了创建部分索引的选项。部分索引提供了稀疏索引功能的超集。如果您使用的是 MongoDB 3.2 或更高版本，部分索引应优先于稀疏索引。

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

更多关于部分索引的文档：https://docs.mongodb.com/manual/core/index-partial/

回答by Samyak Bhuta

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.

实际上，只有“电子邮件”作为字段不存在的第一个文档才能成功保存。不存在“电子邮件”的后续保存将失败并给出错误（请参阅下面的代码片段）。出于这个原因，请在http://www.mongodb.org/display/DOCS/Indexes#Indexes-UniqueIndexes 上查看关于唯一索引和缺失键的 MongoDB 官方文档。

  // 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.

根据定义，唯一索引只能允许一个值仅存储一次。如果您将 null 视为一个这样的值，则它只能插入一次！通过在应用程序级别确保和验证它，您的方法是正确的。这就是它可以做到的。

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

您可能还想阅读此http://www.mongodb.org/display/DOCS/Querying+and+nulls