match eliminating white space mongodb aggregate function-CodePudding

The object in MongoDB database looks like this which includes city names that have spaces in between -

{
    "_id": ObjectId("65ofb9104b1cf1519e4c5957"),
    "country": "us",
    "city": "New York"
}

I would like to make a case insensitive search on the city names eliminating their white spaces. Ex: matching for newyork should return all the documents with New York as a city name. So far, I have tried the following query which does the case insensitive search but it does not eliminate space in between.

db.getCollection('cities').aggregate([
        { $match: { "city": { '$regex':  'newyork' , $options: 'i' } } } , 
]);

CodePudding user response：

There are several ways to accomplish this space/case insensitive search/"$match". Perhaps one of the options below, or a small modification, will satisfy your requirements.

Example collection used with examples below:

  [
    {
      "_id": ObjectId("650fb9104b1cf1519e4c5957"),
      "country": "us",
      "city": "New York"
    },
    {
      "_id": ObjectId("650fb9104b1cf1519e4c5958"),
      "country": "us",
      "city": "Los Angeles"
    }
  ]

This will "$match" your lowercase search string to a space-removed, lowercased "city".

db.cities.aggregate([
  {
    "$match": {
      "$expr": {
        "$eq": [
          {
            "$toLower": {
              "$replaceAll": {
                "input": "$city",
                "find": " ",
                "replacement": ""
              }
            }
          },
          "newyork"
        ]
      }
    }
  }
])

Output:

[
  {
    "_id": ObjectId("650fb9104b1cf1519e4c5957"),
    "city": "New York",
    "country": "us"
  }
]

Try it on mongoplayground.net.

This will "$match" a case insensitive "regex" to a space-removed "city".

db.cities.aggregate([
  {
    "$match": {
      "$expr": {
        "$regexMatch": {
          "input": {
            "$replaceAll": {
              "input": "$city",
              "find": " ",
              "replacement": ""
            }
          },
          "regex": "sang",
          "options": "i"
        }
      }
    }
  }
])

Output:

[
  {
    "_id": ObjectId("650fb9104b1cf1519e4c5958"),
    "city": "Los Angeles",
    "country": "us"
  }
]

Try it on mongoplayground.net.