Need help with validator expression while inserting a document

Working with Data
, ,
#1

I need to write a validator expression while inserting the document in a collection. For example , my collection contains data:

[
  {
    "username": u1,
    emails: [
      {
        type: "work",
        value: u1@email.com,
        primary: true
      },
      {
        type: "work",
        value: u1_1@email.com,
        primary: false
      }
    ]
  },
  {
    "username": u2,
    emails: [
      {
        type: "work",
        value: u2@email.com,
        primary: true
      },
      {
        type: "work",
        value: u2_1@email.com,
        primary: false
      },
     {
        type: "work",
        value: u2_2@email.com,
        primary: false
      }
    ]
  }
]

While inserting new data , I want to validate that the primary email is not duplicate i.e. email mentioned in the object having “primary:true” should not contain in any document’s primary’s email. For example if I insert

{
    "username": u3,
    emails: [
      {
        type: "work",
        value: u2@email.com,
        primary: true
      },
      {
        type: "work",
        value: u2_1@email.com,
        primary: false
      }
    ]
  }

It shouldn’t allow me to insert as user U2 has same primary email. I am struggling with the creation of validator expression. Read about Unique index with partialFilterExpression but having difficulties with that expressions too.

#3

Hello @Vikram_Tanwar ,

Welcome to The MongoDB Community Forums! :wave:

As of now, the partialFilterExpression applies to the document. It does not apply to which array elements will be indexed, so I don’t believe that having more complex partial filter expression would help you with your current schema design. For reference, please see SERVER-17853.

One possible workaround is to record the primary email in a separate top-level field, maybe in addition to the email marked as primary in the array, see an example below

[
  {
    "username": u1,
     primary_email: "u1@email.com",
     primary_email_type: "work"
     other_emails: [
      {
        type: "work",
        value: u1_1@email.com,
      }
    ]
  },
  {
    "username": u2,
     primary_email: "u2@email.com",
     primary_email_type: "work"
     other_emails: [
      {
        type: "work",
        value: u2_1@email.com,
      },
     {
        type: "work",
        value: u2_2@email.com,
      }
    ]
  }
]

Now, let’s try this with an example, I added below documents to my collection

db.collection.insertMany([
  {
    "username": "u1",
    "primary_email": "u1@email.com",
    "primary_email_type": "work",
    "other_emails": [
      {
        "type": "work",
        "value": "u1_1@email.com"
      }
    ]
  },
  {
    "username": "u2",
    "primary_email": "u2@email.com",
    "primary_email_type": "work",
    "other_emails": [
      {
        "type": "work",
        "value": "u2_1@email.com"
      },
      {
        "type": "work",
        "value": "u2_2@email.com"
      }
    ]
  }
]);

Now, to validate that the primary_email field is not a duplicate while inserting new data, I used a unique index with a partial filter expression. Here’s an example of how you can create the unique index:

db.collection.createIndex(
  { "primary_email": 1 },
  { unique: true, partialFilterExpression: { primary_email: { $exists: true } } }
);

This unique index ensures that the primary_email field is unique across the collection only for documents where the field exists. The $exists operator is used in the partial filter expression to match only the documents that have the primary_email field.

With this unique index in place, if you try to insert a new document with a primary_email that already exists, MongoDB will throw a duplicate key error and prevent the insertion.

Note: The unique index will only apply to documents where the primary_email field exists. If you want to ensure uniqueness across all documents, including those where the primary_email field is missing, you can omit the partialFilterExpression option from the index creation command.

Now, let’s run a few test cases to test the uniqueness of the primary_email field:

  1. Inserting a document with a unique primary_email:
db.collection.insertOne({
  "username": "u3",
  "primary_email": "u3@email.com",
  "primary_email_type": "work",
  "other_emails": []
});

Expected result: The document should be inserted successfully without any errors.
Output: Result as expected.

  1. Inserting a document with a duplicate primary_email:
db.collection.insertOne({
  "username": "u4",
  "primary_email": "u1@email.com",
  "primary_email_type": "work",
  "other_emails": []
});

Expected result: The insertion should fail with a duplicate key error since the primary_email value already exists in the collection.
Output: Result as expected.

  1. Inserting a document without a primary_email:
db.collection.insertOne({
  "username": "u5",
  "primary_email_type": "work",
  "other_emails": []
});

Expected result: The document should be inserted successfully since the primary_email field is not present, and the unique index only applies to documents where the field exists.
Output: Result as expected.

These test cases cover scenarios where the primary_email is unique, where it is a duplicate, and where it is not present. You can modify the values and add more test cases as per your requirements.

Let me know if this helps!

Regards,
Tarun

1 Like