0

How to find exact match of multiple text values with IN operation? e.g., 

SELECT * WHERE name in ('george','michael') AND testID in (1,2)

When I try to run below query, it returns extra data for matching values e.g., "the george", not for the exact values only as "george"

{  
   "query":{  
      "bool":{  
         "filter":[  
            {  
               "query_string":{  
                  "fields":[  
                     "name"
                  ],
                  "query":"george or michael"
               }
            },
            {  
               "terms":{  
                  "testID":[1,2]
               }
            }
         ]
      }
   }
}
Kamal Kunjapur
  • 8,547
  • 2
  • 22
  • 32
user923499
  • 305
  • 1
  • 6
  • 18

1 Answers1

1

That is because your field name is of type text.

When datatype if of text, Elasticsearch breaks the values(for e.g The George or George Washington into tokens [the, george] or [george, washington] and saves these tokens in inverted index. Hence when you search on this field using the query you have, it would also return these documents i.e.what you say as not exact match.

For exact match I suggest you make use of keyword type using lowercase normalizer as mentioned in below mapping:

PUT <your_index_name>
{  
   "settings":{  
      "analysis":{  
         "normalizer":{  
            "my_custom_normalizer":{  
               "type":"custom",
               "filter":[  
                  "lowercase"
               ]
            }
         }
      }
   },
   "mappings":{  
      "mydocs":{  
         "properties":{  
            "name":{  
               "type":"keyword",
               "normalizer":"my_custom_normalizer"
            }
         }
      }
   }
}

By doing so your values would be saved as is for e.g. based on above mapping, it would convert The George or George Washington into the george or george washtingon and then store them as is in the inverted index.

You can probably have the query you've mentioned then working as expected.

Hope it helps!

Kamal Kunjapur
  • 8,547
  • 2
  • 22
  • 32
  • Kamal, adding a normalizer would have any performance impact on the whole index while doing search or updating documents in an index? – user923499 Nov 05 '18 at 04:43
  • It would only affect indexing process afaik which I think is acceptable. Keep in mind that if you intend to also have `partial matching`, you may need to implement `multi-field` for your field. https://www.elastic.co/guide/en/elasticsearch/reference/current/multi-fields.html – Kamal Kunjapur Nov 05 '18 at 05:02