Mongodb not respecting unique partial index

Question

I have some records like:

{
  name: "Acme co."
  add4: "",
  nationalNumber: "[phone]"
}, {
  name: "Acme Inc.",
  add4: "6345",
  nationalNumber: ""
}

And I'm attempting to insert them into collection, but only if they are unique. I'm checking for unique by looking for duplicate:

1. nationalNumber (if field isn't empty or null)
2. name + add4 (if both fields aren't empty or null)

I have this code in my nodeJs app:

 await db.collection('mergedcompany').createIndex(
      { nationalNumber: 1},
      { unique: true },
      { partialFilterExpression: {nationalNumber: {$exists: true}}}
      );

    await db.collection('mergedcompany').createIndex(
      { name: -1, add4: -1},
      { unique: true },
      { partialFilterExpression:{ name: {$exists: true}, add4: {$exists: true}}}
      );

And looping over records and inserting:

try {
  await db.collection('mergedcompany').insertOne(record);
} catch (e) {
  sails.log.error(e);
}

All records are getting inserted, even if there are duplicate fields and defined in the unique index. If I remove the "partial" filter from the index, mongo then starts throwing errors like it should. But when I include the partial, no errors are thrown and all records are inserted, regardless if duplicate data.

What do I need to do to make this work?

Answer 1

Apart from the syntax errors, it should work:

db.getCollection('mergedcompany').createIndex(
   { nationalNumber: 1 },
   { unique: true, partialFilterExpression: { nationalNumber: { $exists: true } } }
);

db.getCollection('mergedcompany').createIndex(
   { name: -1, add4: -1 },
   { unique: true, partialFilterExpression: { name: { $exists: true }, add4: { $exists: true } } }
);


db.getCollection('mergedcompany').insertOne({ name: "Acme co.", add4: "", nationalNumber: "[phone]" });
db.getCollection('mergedcompany').insertOne({ name: "Acme co.", add4: "", nationalNumber: "[phone]" });
WriteError({
   "index": 0,
   "code": 11000,
   "errmsg": "E11000 duplicate key error collection: so.mergedcompany index: nationalNumber_1 dup key: { nationalNumber: \"[phone]\" }",
   "op": {
      "_id": ObjectId("601d74357aec96fa0da88319"),
      "name": "Acme co.",
      "add4": "",
      "nationalNumber": "[phone]"
   }
})



db.getCollection('mergedcompany').insertOne({ name: "Acme Inc.", add4: "6345", nationalNumber: "" });
db.getCollection('mergedcompany').insertOne({ name: "Acme Inc.", add4: "6345", nationalNumber: "" });
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: so.mergedcompany index: nationalNumber_1 dup key: { nationalNumber: \"\" }",
    "op" : {
        "_id" : ObjectId("601d74647aec96fa0da8831b"),
        "name" : "Acme Inc.",
        "add4" : "6345",
        "nationalNumber" : ""
    }
})

Note, when you check for { nationalNumber: { $exists: true }} then nationalNumber: "" yields true. You must skip the field entirely.

Answer 2

This will work:

If test document isn't changed after first run an error will appear.

const mongoose = require("mongoose");

const schema = mongoose.Schema({
  name: {
    type: String,
    unique: true,
    required: true,
  },
  add4: {
    type: String,
    unique: true,
    required: true,
  },
  nationalNumber: {
    type: String,
    unique: true,
    required: true,
  },
});

const model = mongoose.model("unique", schema);

const testDocument = {
  name: "Acrhrme co.",
  add4: "th",
  nationalNumber: "+1341rr2h68376",
};

const insertFunc = async () => {
  try {
    await mongoose.connect('mongodb://localhost:27017/test', { useNewUrlParser: true, useUnifiedTopology: true });
    await model.create(testDocument);
  } catch (error) {
    console.error(error);
  }
};

insertFunc().catch(err => {
    console.error(err); 
});