Create a partial index if one key is equal to key in db

[John_Kennedy_Kalu]

I have customer collection with a field hospital id, bvn and phone.

I want to build an index such that if I try to insert a new document and the hospital ids match it should apply a unique constraint to the bvn and phone to ensure the same hospital does not create duplicate data.
example :
doc1 (in db) = {hospital_id: 1, bvn: 1, phone: 1};
if I try to insert a new doc {hospital_id: 1, bvn: 2, phone: 1} or {hospital_id: 1, bvn: 1, phone: 2}; it should flag as duplicate since they have the same hospital_id and therefore the unique attributes have been applied to those fields.
if i try to insert a doc with {hospital_id: 2, bvn: 1, phone: 1}; or {hospital_id: 1, bvn: 2, phone: 2}; it should be successful since the hospital ids are unique or the fields are not duplicated with the same hospital_id as exists on the db.

I tried to do this with compound index but it does not work
hospitalSchema.index({ hospital_id: 1, bvn: 1, phone: 1 }, { key: unique: true });

then with partial index
hospitalSchema.index({ hospital_id: 1, bvn: 1, phone: 1 }, { unique: true, partialFilterExpression: { $eq: this.hospital_id} });

Please help, thank you.

[Aasawari (Aasawari Sahasrabuddhe)]

Hi @John_Kennedy_Kalu and welcome to the MongoDB community!!

If I understand your question correctly, what you need is a unique combination of bvn and phone per hospital_id. Please let me know if my understanding is correct here.

As per the above assumption, you can create unique compound indexes on hospital_id and bvn and hospital_id and phone.

replset [direct: primary] test> db.test.createIndex({hospital:1, bvn:1}, {unique:true})
hospital_1_bvn_1

replset [direct: primary] test> db.test.createIndex({hospital:1, phone:1}, {unique:true})
hospital_1_phone_1

This would restrict inserts that have existing bvn or phone within the same hospital_id

Please refer to the documentations for Unique Compound Index for further understanding.

Let us know if you have any further queries.

Best Regards
Aasawari

[John_Kennedy_Kalu]

Hi @Aasawari and thank you for your response.

I tried your solution however, mongodb applied the unique to the hospital_id so I flipped them around and it worked perfectly.

replset [direct: primary] test> db.test.createIndex({ bvn:1, hospital:1 }, {unique: true})
bvn_1_hospital_1

replset [direct: primary] test> db.test.createIndex({ phone:1, hospital:1 }, {unique: true})
phone_1_hospital_1

Thank you so much!!