MongoDB可选唯一索引 [英] MongoDB Optional Unique Index

问题描述

对于看起来像这样的用户，我有一个MongoDB架构：

I have a MongoDB schema for users that looks something like this:

{
  userId: "some-string",
  anonymousId: "some-other-string",
  project: {"$oid": "56d06bb6d9f75035956fa7ba"}
}

用户必须拥有 userId 或 anonymousId 。当用户属于某个项目时，该模型还有一个名为 project 的引用，它链接到项目集合。

Users must have either a userId or an anonymousId. As users belong to a project, the model also has a reference called project, which links to the project collection.

任何 userId 或 anonymousId 值必须是每个项目唯一的，所以我创建了两个复合索引，如下所示：

Any userId or anonymousId value has to be unique per project, so I created two compound indexes as follows:

db.users.createIndex({ "userId": 1, "project": 1 }, { unique: true })
db.users.createIndex({ "anonymousId": 1, "project": 1 }, { unique: true })

但是，不能同时提供 userId 和 anonymousId ，但要么其中一个，MongoDB为 null 值抛出重复键错误（例如，如果有的话）第二个用户提供anonymousId但没有userId）。

However as not both userId and anonymousId have to be provided but just either one of them, MongoDB throws a duplicate key error for null values (for example if there is a second user with a provided anonymousId but no userId).

因此我试图在复合索引中添加一个稀疏：true 标志，但这显然只有两者都有效字段是空的。我也尝试将稀疏标志仅添加到字段而不是复合索引，但这也不起作用。

I therefore tried to add a sparse: true flag to the compound indexes, but this obviously only works if both fields are empty. I also tried adding the sparse flag only to the fields and not the compound indexes, but this doesn't work either.

举个例子，假设我有跟随集合中的三个用户：

To give an example, let's say I have the following three users in the collection:

{ userId: "user1", anonymousId: null, project: {"$oid": "56d06bb6d9f75035956fa7ba"}}

{ userId: "user2", anonymousId: "anonym", project: {"$oid": "56d06bb6d9f75035956fa7ba"}}

{ userId: "user3", anonymousId: "random", project: {"$oid": "56d06bb6d9f75035956fa7ba"}}

以下应该是可能的：

• 我希望能够插入另一个用户 {userId：user4 ，anonymousId：null} 用于同一个项目（没有出现重复的密钥错误）


• 但是如果我尝试插入另一个 {userId：user3} 或其他用户 {anonymousId：random} 应该有重复的密钥错误

• I want to be able to insert another user {userId: "user4", anonymousId: null} for the same project (without getting a duplicate key error)
• However if I try to insert another user with {userId: "user3"} or another user with {anonymousId: "random"} there should be a duplicate key error

如何我可以实现吗？

推荐答案

如果你使用的是MongoDB 3.2，你可以使用唯一的部分索引而不是稀疏索引。

If you are using MongoDB 3.2, you can use unique partial index instead of sparse index.

实际上建议使用部分索引而不是稀疏索引

Partial index is actually recommended over sparse index

示例

db.users.createIndex({ "userId": 1, "project": 1 }, 
{ unique: true, partialFilterExpression:{ 
  userId: { $exists: true, $gt : { $type : 10 } } } })

db.users.createIndex({ "anonymousId": 1, "project": 1 }, 
{ unique: true, partialFilterExpression:{ 
  anonymouseId: { $exists: true, $gt : { $type : 10 } } } })

在上面的例子中，只有当 userId 存在并且不存在时才会创建唯一索引不包含空值。同样适用于 anonymousId 。

In above example, Unique index will only be created when userId is present and doesn't contain null value. Same holds true to anonymousId too.

请参阅 https://docs.mongodb.org/manual/core/index-unique/#unique-partial-indexes

这篇关于MongoDB可选唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！