Best practice for phone number validation?

How can I make sure a phone number is unique, but also empty string is allowed?
This is the current scheme, but obviously it’s not working:

(Node.js, Express, Mongoose)

    phoneNumber: {
      type: String,
      unique: [true, "Phone number is already in use."],
      validate: {
        validator: validatePhoneNumber,
        message: "Invalid phone number.",
      },
      default: "",
    },

With this validator and regex:

export const phoneNumberRegex = /^05\d{8}$/;  // Like 0526665656

export const validatePhoneNumber = (phoneNumber) => {
  return phoneNumberRegex.test(phoneNumber);
};

Thanks :innocent:

Would appreciate someone’s help :pensive:

Hey, welcome to the MongoDB community.

I believe this will help you to maintain your logic

db.collectionName.createIndex(
   {phoneNumber: 1},
   {unique: true, partialFilterExpression: {phoneNumber: {"$gt":""}}}
);

I created a small lab for you to understand the only point of attention.

List all documents:

test> db.collectionName.find()
[
  {
    _id: ObjectId("650d8d918af6e1afae571da1"),
    name: 'John',
    phoneNumber: '1234567890'
  },
  {
    _id: ObjectId("650d8d948af6e1afae571da3"),
    name: 'John',
    phoneNumber: ''
  },
  {
    _id: ObjectId("650d8d948af6e1afae571da4"),
    name: 'John',
    phoneNumber: ''
  }
]

The partial index will not be used when you filter for null values, as the expression must be greater than “”

test> db.collectionName.find({ "phoneNumber": ""}).explain();
{
  explainVersion: '2',
  queryPlanner: {
    namespace: 'test.collectionName',
    indexFilterSet: false,
    parsedQuery: { phoneNumber: { '$eq': '' } },
    queryHash: '0EFD98BB',
    planCacheKey: 'DF014AED',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      queryPlan: {
        stage: 'COLLSCAN',
        planNodeId: 1,
        filter: { phoneNumber: { '$eq': '' } },
        direction: 'forward'
      },
      slotBasedPlan: {
        slots: '$$RESULT=s5 env: { s7 = "", s2 = Nothing (SEARCH_META), s3 = 1695387225258 (NOW), s1 = TimeZoneDatabase(Etc/GMT+1...Asia/Nicosia) (timeZoneDB) }',
        stages: '[1] filter {traverseF(s4, lambda(l1.0) { ((l1.0 == s7) ?: false) }, false)} \n' +
          '[1] scan s5 s6 none none none none lowPriority [s4 = phoneNumber] @"3591b1ec-a577-4ba5-8206-44ebbcf3f97a" true false '
      }
    },
    rejectedPlans: []
  },
  command: {
    find: 'collectionName',
    filter: { phoneNumber: '' },
    '$db': 'test'
  },
  serverInfo: {
    host: 'e10d44068e0f',
    port: 27017,
    version: '7.0.1',
    gitVersion: '425a0454d12f2664f9e31002bbe4a386a25345b5'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,
    internalQueryFrameworkControl: 'trySbeEngine'
  },
  ok: 1
}

If you pass the number, you can use the index for your query, as you need to meet its filter for it to be in the index.

test> db.collectionName.find({ "phoneNumber": "1234567890"}).explain();
{
  explainVersion: '2',
  queryPlanner: {
    namespace: 'test.collectionName',
    indexFilterSet: false,
    parsedQuery: { phoneNumber: { '$eq': '1234567890' } },
    queryHash: '0EFD98BB',
    planCacheKey: 'C344E9E5',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      queryPlan: {
        stage: 'FETCH',
        planNodeId: 2,
        inputStage: {
          stage: 'IXSCAN',
          planNodeId: 1,
          keyPattern: { phoneNumber: 1 },
          indexName: 'phoneNumber_1',
          isMultiKey: false,
          multiKeyPaths: { phoneNumber: [] },
          isUnique: true,
          isSparse: false,
          isPartial: true,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: { phoneNumber: [ '["1234567890", "1234567890"]' ] }
        }
      },
      slotBasedPlan: {
        slots: '$$RESULT=s11 env: { s3 = 1695387212539 (NOW), s6 = KS(3C3132333435363738393000FE04), s2 = Nothing (SEARCH_META), s5 = KS(3C31323334353637383930000104), s10 = {"phoneNumber" : 1}, s1 = TimeZoneDatabase(Etc/GMT+1...Asia/Nicosia) (timeZoneDB) }',
        stages: '[2] nlj inner [] [s4, s7, s8, s9, s10] \n' +
          '    left \n' +
          '        [1] cfilter {(exists(s5) && exists(s6))} \n' +
          '        [1] ixseek s5 s6 s9 s4 s7 s8 [] @"3591b1ec-a577-4ba5-8206-44ebbcf3f97a" @"phoneNumber_1" true \n' +
          '    right \n' +
          '        [2] limit 1 \n' +
          '        [2] seek s4 s11 s12 s7 s8 s9 s10 [] @"3591b1ec-a577-4ba5-8206-44ebbcf3f97a" true false \n'
      }
    },
    rejectedPlans: []
  },
  command: {
    find: 'collectionName',
    filter: { phoneNumber: '1234567890' },
    '$db': 'test'
  },
  serverInfo: {
    host: 'e10d44068e0f',
    port: 27017,
    version: '7.0.1',
    gitVersion: '425a0454d12f2664f9e31002bbe4a386a25345b5'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,
    internalQueryFrameworkControl: 'trySbeEngine'
  },
  ok: 1
}

If necessary, you can add more fields to the index to meet your workload, here is just an example of how to meet your problem.
I’m available :grinning:

1 Like