MongoDB and pymongo: specify `ignore case` flag for `$in` operator

Question

I need to run raw query with MongoDB. Now my $in filter in pymongo looks like this:

{'category': {'$in': ['fruits', 'vegetables']}}

But it has revealed that sometimes category is Fruits and filter does not match that value. I cannot fix all category data in database because there is every day syncing and there are too many checks already. So, the only way is to improve the filter (it's not only category in this filter, by the way).

So I wonder is there any way to ignore case with $in operator?

To be totally clear, what I have now:

filter:

{'category': {'$in': ['fruits', 'vegetables']}}

data:

{'category': 'fruits'} // match
{'category': 'Fruits'} // does not match

What I need:

filter:

{'category': {'$ignorecase_in': ['fruits', 'vegetables']}}

data:

{'category': 'fruits'} // match
{'category': 'Fruits'} // match

score 1 · Answer 1 · answered Aug 05 '21 at 17:27

You can use regex i flag which will make regex case insensitive .

Like in your example.

 {'category': {'$in': [new RegExp('fruits', "i" ) , new RegExp('vegetables', "i")]}}

I showed you the way as an example. You can apply the DRY approach to not repeat new Regexp for every elements.

score 0 · Answer 2 · answered Aug 05 '21 at 17:58

SHORTLY,

for pymongo use re.compile with IGNORECASE flag:

from re import compile, IGNORECASE

items.find({'category': {
    '$in': [
        compile('fruits', flags=IGNORECASE),
        compile('vegetables', flags=IGNORECASE)
    ]
}})

IN DETAILS,

@me-on1's approach is correct.

$in documentation says that $regex operator cannot be used inside $in (otherwise you'll get cannot nest $ under $in exception):

The $in operator can specify matching values using regular expressions of the form /pattern/. You cannot use $regex operator expressions inside an $in.

Consider the following example:

db.inventory.find( { tags: { $in: [ /^be/, /^st/ ] } } )

So if I'm allowed to use regular expressions inside $in the real question is how to do it in python? Because python does not support /pattern/ form.

Then, I have found a similar question and similar answer for python.

So all I had left was to specify ignore case flag:

from re import compile, IGNORECASE

items.find({'category': {
    '$in': [
        compile('fruits', flags=IGNORECASE),
        compile('vegetables', flags=IGNORECASE)
    ]
}})

MongoDB and pymongo: specify `ignore case` flag for `$in` operator

2 Answers2

SHORTLY,

IN DETAILS,