My MongoDB database contains approximately 500 million documents structured like this:
{
"_id": objectId,
"Name": "John Smith",
"Address": "132, My Street, Kingston, New York 12401"
}
I am looking to query the data based on both the Name Regex and Address Regex. The issue is that sometimes the name and address are case-sensitive in the database. I have tried creating separate indexes for each field, as well as a compound index with text (but it only searches properly if the text index is on one field - either Address or Name).
I also attempted to create an index for both fields like so: createIndex({Name: 1, Address: 1}); This approach returns data even if I search using just one field. What I want is to be able to perform a query like:
var name = "john smith";
var address = "new york";
var userData = await db
.collection("userData")
.find({
$and: [
{ Name: { $regex: new RegExp(name, "i") } },
{ Address: { $regex: new RegExp(address, "i") } },
],
})
.skip(skip)
.limit(limitNumber)
.toArray();
When using a single index, I can still query with one field but not with both simultaneously. Trying all the above methods with both fields causes extreme loading times, even after trying to optimize the query execution time. It doesn't return any results or errors.
https://i.sstatic.net/OlmBCcr1.png
Thanks