In Mongodb, after create the text index, when query using text filter there is no any output showing

Question

In Mongodb, I have created the collection as follows.

db.test.insertMany([
{CustomerKey : "11026", FirstName : "Harold", LastName : "Sai", BirthDate : new Date("1951-10-1"),MaritalStatus : "S", Gender : "M", EmailAddress : "harold3@adventure-works.com", YearlyIncome : 30000, TotalChildren : 2, NumberChildrenAtHome : 0, EnglishEducation : "Partial College", EnglishOccupation : "Clerical", NumberCarsOwned : 2, AddressLine1 : {House_No : 2596, Area_Name: "Franklin Canyon Road"}, Phone : "1 (11) 500 555-0131", DateFirstPurchase : new Date("2011-10-1"), CommuteDistance : "1-2 Miles"} ,
{CustomerKey : "11027", FirstName : "Jessie", LastName : "Zhao", BirthDate : new Date("1952-6-5"),MaritalStatus : "M", Gender : "M", EmailAddress : "jessie16@adventure-works.com", YearlyIncome : 30000, TotalChildren : 2, NumberChildrenAtHome : 0, EnglishEducation : "Partial College", EnglishOccupation : "Clerical", NumberCarsOwned : 2, AddressLine1 : {House_No : 8211, Area_Name: "Leeds Ct."}, Phone : "1 (11) 500 555-0184", DateFirstPurchase : new Date("2011-6-1"), CommuteDistance : "5-10 Miles"} ,
{CustomerKey : "11028", FirstName : "Jill", LastName : "Jimenez", BirthDate : new Date("1951-10-9"),MaritalStatus : "M", Gender : "F", EmailAddress : "jill13@adventure-works.com", YearlyIncome : 30000, TotalChildren : 2, NumberChildrenAtHome : 0, EnglishEducation : "Partial College", EnglishOccupation : "Clerical", NumberCarsOwned : 2, AddressLine1 : {House_No : 213, Area_Name: "Valencia Place"}, Phone : "1 (11) 500 555-0116", DateFirstPurchase : new Date("2011-10-1"), CommuteDistance : "1-2 Miles"} ,
]);

Following is the output of query :(emailaddress with Harold Available)

I have set "EmailAddress" field as Text Index.

 db.test.createIndex({EmailAddress : "text"})

But When i Query using the following code, there is no any output for text filter.

db.test.find({$text:{$search:"harold"}})

score 2 · Answer 1 · answered Aug 19 '21 at 13:05

2

What you are looking for is

db.test.find({"EmailAddress":{"$regex":"harold"}})

As you are looking for some sort of pattern match.

A text index stores the field in a tokenised form by removing stop words, replacing words by their stem words etc

You can read more about it here https://docs.mongodb.com/manual/text-search/#-text-operator

regex operator and its index use: https://docs.mongodb.com/manual/reference/operator/query/regex/

answered Aug 19 '21 at 13:05

Sharang Chopra

226
1
10

A text index stores the field in a tokenized form by removing stop words, replacing words by their stem words etc// Can u explain this further, please? I go through the documentation. not exactly got the point. – viththi Aug 19 '21 at 13:21
Actually, my target is to have a score using the text index method. But following command does not output anything. is there any method to use the text index method to filter "harold" in email address. db.Customer.find({$text:{$search:"harold"}},{score:{$meta:"textScore"}}).pretty() – viththi Aug 19 '21 at 13:23

score 1 · Accepted Answer · answered Aug 19 '21 at 18:12

Text indexes do not support partial word matches. They are expected to find the whole word in a sentence. In your example harold is considered as part of the word harold3@adventure-works.com thus you are trying to perform a partial word match. Consider the following document as a test case...

db.test.insert({
    "CustomerKey" : "11026",
    "FirstName" : "Harold",
    "LastName" : "Sai",
    "BirthDate" : ISODate("1951-10-01T00:00:00Z"),
    "MaritalStatus" : "S",
    "Gender" : "M",
    "EmailAddress" : "harold is at harold3@adventure-works.com",
    "YearlyIncome" : 30000,
    "TotalChildren" : 2,
    "NumberChildrenAtHome" : 0,
    "EnglishEducation" : "Partial College",
    "EnglishOccupation" : "Clerical",
    "NumberCarsOwned" : 2,
    "AddressLine1" : {
        "House_No" : 2596,
        "Area_Name" : "Franklin Canyon Road"
    },
    "Phone" : "1 (11) 500 555-0131",
    "DateFirstPurchase" : ISODate("2011-10-01T00:00:00Z"),
    "CommuteDistance" : "1-2 Miles"
})

... now, your original query will find it because the whole word harold is found in the field EmailAddress.

While Text indexes do no support partial word matches they will allow word-stemming. For example if you search on run, it will find running.

Another option is to use MongoDB Atlas. Atlas supports Apache Lucene based search indexes which provide partial and fuzzy match capabilities.

For another reference to a similar SO article see MongoDB Full and Partial Text Search.

In Mongodb, after create the text index, when query using text filter there is no any output showing

2 Answers2