Mongoose 复合索引唯一+稀疏

问题描述

我想创建一个索引，以确保在制造商 + art的组合中没有重复的serialNr.但是有些项目没有serialNr.这些我不想检查/索引.

I want to create an index which ensures, that I don't have a duplicate serialNr within the combination of a manufacturer + art. But some items don't have a serialNr. These I don't want to check/index.

mySchema.index({ serialNr: 1, art: 1 ,制造商: 1, 删除: 1}, { unique: true, sparse: true)

我也尝试过添加部分过滤器:partialFilterExpression: { serialNr: {$ne:null} } 到索引选项.

I tried it also with adding a partial Filter:partialFilterExpression: { serialNr: {$ne:null} } to the index options.

我如何索引它插入:[{art: 'a', Manufacturer:'a', deleted:null}, {art: 'a',制造商:'a', deleted:null}] 通过

How can I index it that inserting:[{art: 'a', manufacturer:'a', deleted:null}, {art: 'a', manufacturer:'a', deleted:null}] passes

和

[{serialNr: '123', art: 'a', 制造商:'a', 删除:null}, {serialNr: '123', art: 'a',制造商:'a',删除:空}]失败

我不想保存:[{serialNr: null, art: 'a',制造商:'a', 删除:null}, {serialNr: null, art: 'a',制造商:'a', 删除:null}]

mongo 文档声明，如果复合索引中缺少字段，则不应对其进行索引:

The mongo docs state, that it should not be indexed if a field is missing in a compound index:

稀疏索引仅根据索引字段的存在，或者对于复合索引，存在的索引字段.https://docs.mongodb.org/manual/core/index-partial/

新索引

implantSchema.index({ serialNr: 1, art: 1 ,制造商: 1, 删除: 1}, { unique: true, partialFilterExpression: { serialNr: {$exists:true} }})

在我对索引进行更改之前，我将我的 collection 放在我的开发数据库中，以确保不存在旧索引.这是在 db.items.getIndexes()

Before I make changes on the index I drop my collection in my dev-database, to ensure that there is no old index existing. This is the new index which is returned on db.items.getIndexes()

/* 0 */
{
    "0": {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "LOC_db.items"
    },
    "1": {
        "v" : 1,
        "unique" : true,
        "key" : {
            "serialNr" : 1,
            "art" : 1,
            "manufacturer" : 1,
            "deleted" : 1
        },
        "name" : "serialNr_1_art_1_manufacturer_1_deleted_1",
        "ns" : "LOC_db.items",
        "partialFilterExpression" : {
            "serialNr" : {
                "$exists" : true
            }
        },
        "background" : true
    }
}

数据

• 我有一个预保存钩子，我在其中检查 serialNr 的值，该值以前不存在，返回 dup 错误.
• 当查询数据库时，没有 doc 存在，其中 null, " 作为 serialNr.
• 查询 db.items.find({ serialNr: {$exists:true} }) 不匹配任何项目.

Data

• I have a pre save hook where I check the value of serialNr, which does not exist before, the dup error is returned.
• When querying the database there is no doc existing which has null, "" as value for the serialNr.
• The query db.items.find({ serialNr: {$exists:true} }) does not match any items.

insertDocument :: caused by :: 11000 E11000 duplicate key error index: LOC_db.items.$serialNr_1_art_1_manufacturer_1_deleted_1 dup key: { : null, : "Robo", : "Olymp", : null }

附加信息

错误可能与:https://github.com/Automattic/mongoose/issues/2457

推荐答案

你实际上已经很接近解决方案了，所有你需要在创建索引时使用它 :

You were actually quite close to the solution, all you need to use this when creating index :

partialFilterExpression: { serialNr: {$exists:true} }

确保至少有 3.2 版本的 mongo

---

Be sure to have at least 3.2 version of mongo

示例:

db.col.createIndex({a:1, b:1},{unique:true, partialFilterExpression: { a: {$exists:true} })
db.col.save({a:50, b:27}) //pass
db.col.save({a:50, b:27}) //fails
db.col.save({b:27}) //pass
db.col.save({b:27}) //pass
db.col.save({b:27}) //pass
db.col.save({a:50, b:27}) //fails
db.col.save({a:50}) //pass
db.col.save({a:50}) //fails

PS:也许你可以做一点hack"并使用它:partialFilterExpression: { serialNr: {$exists:true}, serialNr: {$gte:0} }

我尝试使用 $ne，但它给我带来了错误.

I tried using $ne, but it was throwing me an error.

这篇关于Mongoose 复合索引唯一+稀疏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！