Multiple $regex using $and in MongoDB – This article will take you through the common SQL errors that you might encounter while working with sql, regex, mongodb. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have some collection with the next records:
{
"_id" : ObjectId("52b18fb2a21351b2bb29dfc7"),
"title" : "aaa"
}
{
"_id" : ObjectId("52b18fd0d17d7f69e078f7b7"),
"title" : "bbb"
}
{
"_id" : ObjectId("52b18fd3d17d7f69e078f7b8"),
"title" : "ccc"
}
Next query gives as a result 1 records (with title=”aaa”) as we expected:
db.test.find({
{title:{$regex:'aaa'}}
})
But when we use complex condition for $and we got something unexpected:
db.test.find({
$and: [
{title:{$regex:'aaa'}},
{title:{$regex:'bbb'}}
]
})
I need query exactly in this case because I’m going to use selection with stop-words, for example:
db.test.find({
$and: [
{title:{$regex:'aaa'}},
{title:{$regex:'bbb'}},
{title:{$not:/bbb/i}},
]
})
Using query that above, I expecting only one field in result (with title=”aaa”).
I have idea how to solve this issue using aggregate, but I hope there is another way how to solve it.
Thanks!
Solution :
Simply build proper regex
pattern "/A AND B/"
pattern "/NOT (NOT A OR NOT B)/"
Regex:
"/^(^A|^B)/"
Or this one
/(?=.*word1)(?=.*word2)/
Well, tested using another data and got expected result:
/* 0 */
{
"_id" : ObjectId("52b18fb2a21351b2bb29dfc7"),
"title" : "aaa"
}
/* 1 */
{
"_id" : ObjectId("52b18fd0d17d7f69e078f7b7"),
"title" : "bbb"
}
/* 2 */
{
"_id" : ObjectId("52b18fd3d17d7f69e078f7b8"),
"title" : "ccc"
}
/* 3 */
{
"_id" : ObjectId("52b19606d17d7f69e078f7b9"),
"title" : "aaa test"
}
/* 4 */
{
"_id" : ObjectId("52b19624d17d7f69e078f7ba"),
"title" : "aaa test wel"
}
Query:
db.test.find({
$and: [
{title:{$regex:'aa'}},
{title:{$regex:'t'}},
{title:{$not:/wel/}}
]
})
Response:
/* 0 */
{
"_id" : ObjectId("52b19606d17d7f69e078f7b9"),
"title" : "aaa test"
}
Perhaps, that issue reproducing only when title and conditions contains cyrillic symbols. Going to to reproduce it now..
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.