Fidning a complete term among different fields for Atlas Search

Question

I am trying to use Atlas search to search for a name. In my dataset name is divided between FirstName and LastName, and that causes the problem for me.

I have tried the following stage in my aggregation:

{
    "$search" : { 
        "phrase" : { 
            "query" : "John Doe", 
            "path" : ["FirstName", "LastName"] 
        } 
    } 
},

This does not find any results even if I have a document with FirstName = "John" and LastName = "Doe". If I search for John or Doe, I get this result. I have also tried to use $text instead of Phrase, and even if i then get John Doe when I do that search I also get any other document that has FirstName = John or LastName = Doe, so I get a lot more hits than I want.

So basically I want my search to return "John Doe", "John William Doe", but not "Frank Doe" or "John Williams".

I really don't want to introduce a new field in the database for this just to combine the FirstName and LastName, so hopefully someone has a nice solution to this!

Answer 1

Maybe you could split user input on spaces and use the compound operator with two must clauses. Here's what the above query should look like:

"must": [
          {
            "test": {
              "query": "John",
              "path": "FirstName"
            }
          },
          {
            "text": {
              "query": "Doe",
              "path": "LastName"
            }
          }
        ],
        "minimumShouldMatch": 2 // specifying that we need both fields to match
      }