exact match with IN Operator in elasticsearch

Question

asked Jul 18, 2019 in AWS by yuvraj (19.1k points)

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

SELECT * WHERE name in ('charlie','alan') AND testID in (1,2)

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

{
   "query":{
      "bool":{
         "filter":[
            {
               "query_string":{
                  "fields":[
                     "name"
                  ],
                  "query":"charlie or alan"
               }
            },
            {
               "terms":{
                  "testID":[1,2]
               }
            }
         ]
      }
   }
}

1 Answer

kodee · Answer 1 · 2019-07-18T10:36:46+0000

The reason behind this is because your field name is of type text.

Elasticsearch breaks the values when the data type is text. For instance, if you provide The Charlie or Charlie Puth, it will take the values like this [the, charlie] or [charlie, path] and it will be saved in the inverted index. So, right now if you query, it will return these documents which you consider to not match.

You can solve this by using lowercase normalizer. The mapping looks like this:

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

Now, the data entered like The Charlie will be stored as the charlie and you stored in an inverted index. So, now you can query and get data as you expect.

exact match with IN Operator in elasticsearch

1 Answer

Related questions

Browse Categories