MongoDB: how to find documents ignoring case sensitive, accents and percent like logic (%)

Question

I would like to make a search on a collection in my mongodb database. In my collection, I have documents with the field "name" can be values like:

[i] "Palácio Guanabara", "Palácio da Cidade", "Festa Palácio", etc.

When a user types a search like "pala" or "palá" or "Pala" or "PalÁ", all those itens in [i] must build the result set.

I found that in MongoDB I could use regex in searches, like:

{ "name": { $regex: new Regex(".*pala.*", "i") } }

Ok, this approach is case insensitive and use the percent like logic from SQL ("%pala%"). But, it isn't ignore accents from the register in database.

I found another alternative with the $text index: https://docs.mongodb.org/manual/core/index-text/

This approach can ignore case sensitive and accents. But the "search" does not accepts a regex, so I can't search things like "%pala%".

Summing up, I want to make the following SQL query in MongoDB:

select * from collection where remove_accents(upper(name)) like '%Pala%'

And this query returning results with name like "palácio", "palacio", "PaláCiô", etc.

Answer 1

This works for me!

Just replace vowels with a regex which contains accents vowels. Additionally can use $options: 'i' to avoid case sensitive.

function diacriticSensitiveRegex(string = '') {
         return string.replace(/a/g, '[a,á,à,ä,â]')
            .replace(/e/g, '[e,é,ë,è]')
            .replace(/i/g, '[i,í,ï,ì]')
            .replace(/o/g, '[o,ó,ö,ò]')
            .replace(/u/g, '[u,ü,ú,ù]');
    }

find ({ name: { $regex: diacriticSensitiveRegex('pala'), $options: 'i' } });

Answer 2

what happened if you use just :

find({name: {$regex: 'pala', $options: "i"}})

you used new Regex() that may not valid constructor valid constructor is new RegExp()