Search for string that doesn't start with a certain prefix using regex index-CodePudding

We are trying to use the index when excluding records with a fields that starts with a certain prefix, when using the following, index is not being used:

{"field": {"$not": {"$regex": "^abcd"}}}

And it takes forever to get the result.

But when using:

{"field": {"$regex": "^abcd"}}

The index is used and we get the result instantly!

Is there a way around this?

CodePudding user response：

I'm not expert on regex but I think you can use ^(?!abcd) like this:

db.collection.find({
  "field": {
    "$regex": "^(?!abcd)"
  }
})

Example here

CodePudding user response：

If the string is only alpha-numeric, you can use inequalities and avoid the regex altogether:

{$or: [{"field": {"$lt": "abcd"}}, {"field": {"$gt": "abcd~"}}]}

Since ~ is greater than z, that query will return all values that don't start with that prefix, as long as ~ is not a valid character in the value you are examining.

Playground