Trouble making a query with Elasticsearch

Question

Well guys I hope you're doing fine in this epidemic times, I'm having trouble in neglecting special characters in a query at elasticsearch : Here is what I want to do :

Select * from table where ext like %6500% and start_time like %-01-% 

Here is what I did:

   "query": {
       "bool": {
           "must": [
               {
                   "query_string": {
                       "ext": "*6500*",
                       "fields": [
                           "extension"
                       ],
                       "analyze_wildcard": true
                   }
               },
               {
                   "query_string": {
                       "query": "*\\-01\\-*",
                       "fields": [
                           "start_time"
                       ],
                       "analyze_wildcard": true
                   }
               }
           ]
       }
   }

The first one works but the second doesn't give what I want. Btw the field start_time is like this for example: 2020-01-03 15:03:45 and it's a heyword type (I found it like that).

Answer 1

You are indexing your field with type text and sub fields of keyword type. Text fields are broken in tokens ex "2020-01-12" will be stored as ["2020","01","12"]. You need to run your query on keyword field using "start_time.keyword"

{
  "query": {
       "bool": {
           "must": [
               {
                   "query_string": {
                       "query": "*-01-*",
                       "fields": [
                           "start_time.keyword" --> note
                       ],
                       "analyze_wildcard": true
                   }
               }
           ]
       }
   }
}

As @joe mentioned wildcard queries have poor performance it is better to use date field