I have a set of data that looks like this:
{
name: "Acme co."
add4: "",
nationalNumber: "+13412768376"
}, {
name: "Acme Inc.",
add4: "6345",
nationalNumber: ""
}My goal is to insert these records into a collection, but only if they are unique. To determine uniqueness, I need to check for duplicates based on:
- nationalNumber (if the field is not empty or null)
- name + add4 (if both fields are not empty or null)
In my Node.js application, I have the following code:
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}}}
);I am looping through the records and inserting them using the following code:
try {
await db.collection('mergedcompany').insertOne(record);
} catch (e) {
sails.log.error(e);
}However, all records are being inserted even when there are duplicate values as defined in the unique index. When I remove the "partial" filter from the index, MongoDB throws errors as expected. But with the partial filter included, no errors are thrown and all records are still inserted, regardless of duplicate data.
What steps should I take to resolve this issue?