find phoneNumber regex with mongoose-CodePudding

I am trying to build a query to find phoneNumber in MongoDB. for example I have those number in my database:

33671974813
33.6.71.97.48.13
33 6 71 97 48 13

and I want to find them all in a query like

mobilePhone: { $regex: " 3367197", $options: "i" }

I already try that but it doesn't work:

mobilePhone: { $regex: " 3367197", $options: "i", $in: [/^\ ?[\d\. ] /] }

Do you know how to do ?

Thanks for help ;)

CodePudding user response：

I think you should clean the database from bad formatted phones. Also dont allow bad formatted phones to enter the database, clean them before insert.

The bellow query works in bad formated phones.
The first step is to clean the phone (keep only digits and the symbol if in front). And then does the match you want.

You can use the first part that cleans the phone, to clear all the database, with a pipeline update, like updateMany({},[{"$set" ...}])

Also when you try to match you should escape the regex special characters, for example not {regex : " 33.."} but {regex : "\\ 33"} like the example bellow.

For a function that does those automatically see this answer also

Query

Test code here

db.collection.aggregate([
  {
    "$set": {
      "phone": {
        "$reduce": {
          "input": {
            "$regexFindAll": {
              "input": "$phone",
              "regex": "^\\ |\\d "
            }
          },
          "initialValue": "",
          "in": {
            "$concat": [
              "$$value",
              "$$this.match"
            ]
          }
        }
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$regexMatch": {
          "input": "$phone",
          "regex": "\\ 3367197"
        }
      }
    }
  }
])