I use various indexed collections in MongoDB where I make queries from a simple search engine. The point of the matter is that I can't find a way to do these Regex queries in case insensitive, i.e. the queried collection doesn't follow the index.
this is the situation:(This way the index is followed by the collection without problems)
var query = req.query.name;
function(callback) {
collection3.find({$or:[{ firstname: new RegExp('^'+query)},
{ email: new RegExp('^'+query)}]}).toArray(function(err, result3){
if (err) return callback(err);
locals.result3 = result3;
callback();
});
},
.........
.........
I have tried in all possible ways but I cannot get the desired result, and I cannot find information specific to my problem, I have tried with:
collection3.find({$or:[{ firstname: new RegExp('^' +query+ '$', 'i')}, ......
collection3.find({$or:[{ firstname: new RegExp('^' +query,'i')}, ......
collection3.find({$or:[{ firstname: { $regex: query, $options: 'i' }},......
collection3.find({$or:[{ firstname: { $regex: new RegExp(`^${query}$`), $options: 'i'}},......
I also tried to bind it from the variable in various ways including:
var value = '^'+query;
var value = /^query/i;
` In short, since there does not seem to be one solution I would be grateful to anyone who helps me!
other ways of dealing with the problem would also be great Thanks!
Try this:
var query = req.query.name;
function(callback) {
collection3.find({
$or:[
{
"firstname": {
"$regex": `^${query}$`,
"$options": "i"
}
},
{
"email": {
"$regex": `^${query}$`,
"$options": "i"
}
}
]
}).toArray(function(err, result3){
if (err) return callback(err);
locals.result3 = result3;
callback();
});
}
Here is a working snippet: https://mongoplayground.net/p/mfmS7ugsWqr
If you just need a case insensitive search, use a collation. Create the indexes with the collation specifying case-insensitive:
collection3.createIndex({name: 1},{collation: {locale: "en", strength: 1, caseLevel: false}} )
collection3.createIndex({name: 1},{collation: {locale: "en", strength: 1, caseLevel: false}} )
And use the same collation when querying:
collection3.find({$or:[{name:query},{email:query}]}).collation({locale:"en",strength:1,caseLevel:false})
UPDATE
For those who find themselves in a similar problem, I temporarily solved (based on my need) by automatically setting the first letters of the name in uppercase before making the request in the req.query.name (luckily the names all start in capital letters)
var query = req.query.name;
var value = '^'+query;
function toTitleCase(str) {
return str.replace(
/\w\S*/g,
function(txt) {
return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
}
);
}
.............
.............
subsequently in place of '^' + query i added in new RegExp toTitleCase(value)
function(callback) {
collection3.find({$or:[{ firstname: new RegExp(toTitleCase(value))},
{ email: new RegExp('^'+query)}]}).toArray(function(err, result3){
if (err) return callback(err);
locals.result3 = result3;
callback();
});
},
.........
.........
in this way, even if I write in lowercase in the search bar, the request in req.query.name is made in uppercase only with the first letters of the beginning of each word. leaving unchanged email, since they are obligatorily in lower case, and the whole follows the index perfectly!
I was lucky because I have this syntax (the first letter always in uppercase) thus bypassing the problem, otherwise another idea was to bring the entire database back to lowercase at the root! It amazes me that to date there is no solution to this problem, at least as far as I am aware!
PS: If you have any other ideas, it would be welcome! thanks