Mongodb has 500 million documents and i want to search with regEx based on two fields in javascript driver not schema

Question

My MongoDb has around 500 million documents looking like the following:

{
  "_id": objectId,
  "Name": "John Smith",
  "Address": "132, My Street, Kingston, New York 12401"
}

Now i want to query the data based on the Name Regex and Address Regex as sometimes the name is case sensitive in my database and sometimes address too. I have tried creating separate index (1) for both fields, also tried creating compound index with text (in text index the problem is it only searches if the text index was on one field either Address or Name doesn't work with both field's index with text).

Also tried both fields index as: createIndex({Name: 1, Address: 1}); <--- this returns data also if i search with one field only. I want 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();

If i try with single index i can still perform query with one field not with both fields. I tried both fields with all above methods it takes forever to load even i tried extending the query ms time still doesn't return anything or even an error.

Thanks

Answer 1

Try this one:

var userData = await db
  .collection("userData")
  .find({
      Name: { $regex: name, $options: "i" },
      Address: { $regex: address, $options: "i" } 
  })

$and operator is not needed.

If you like to use it, then you must use it with $expr

var userData = await db
  .collection("userData")
  .find({ $expr: {
    $and: [
      { $regexMatch: { input: "$Name", regex: name, options: "i" } },
      { $regexMatch: { input: "$Address", regex: address, options: "i" } }
    ],
  }})