Remove special characters in mongo aggregate while query run

Question

I would like to get all the datas from mongo while i need to replace special characters, but not rewrite the data while the query run:

data in db:

[{
number: "12/34",
type: "string"
},
{
number: "56-78",
type: "string"
},
{
number: "910*2",
type: "string"
}]

the number what I would like to query is: 1234, 5678

the related output is:

[{
number: "12/34",
type: "string"
},
{
number: "56-78",
type: "string"
}]

what I try is to add field temporary without special characters, but I cannot remove all of them because addfield doesn't handle regex. i try to do this with reduce but it's not work for me. Anybody can help me?

Answer 1

With MongoDB v4.2+, you can use $regexFindAll to locate all digits. Use $reduce and $concat to reconstruct a string with special characters removed/sanitized. Perform the search on the sanitized string.

db.collection.aggregate([
  {
    "$addFields": {
      "sanitized": {
        "$regexFindAll": {
          "input": "$number",
          "regex": "\\d"
        }
      }
    }
  },
  {
    "$addFields": {
      "sanitized": {
        "$reduce": {
          "input": "$sanitized.match",
          "initialValue": "",
          "in": {
            "$concat": [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    "$match": {
      $expr: {
        "$in": [
          "$sanitized",
          [
            "1234",
            "5678"
          ]
        ]
      }
    }
  },
  {
    "$project": {
      sanitized: false
    }
  }
])

Here is the Mongo playground for your reference.