Elasticsearch. Range query for strings with dashes

Question

I'm using elasticsearch with data that have string-field with date values, like this:

"2016-01-25 18:40:18.933"

I'm trying to use range filter for getting values from date to date. For example:

    "query" : {
        "filtered" : {
            "query" : {
                "range" : {
                    "createdDate" : {
                        "gte": "2015-11-01", 
                        "lte": "2016-01-25"
                    }
                }
            }
        }
      }
    }

But results doesn't contain values with "createdDate": "2015-12-14 20:28:23.557"

If I use "gte": "2015" or "gte": "2014-12-31", then values with "createdDate": "2015-12-14" will be included in the results.

What's wrong in my query?

Can you share the mapping of your createdDate field? curl -XGET localhost:9200/index/_mapping — Val
– Val, Commented Jan 27, 2016 at 11:23
{ "documents": { "mappings": { "order": { "properties": { ... "createdDate": { "type": "string" } — M.ax
– M.ax, Commented Jan 27, 2016 at 11:50

Val · Accepted Answer · 2016-01-27 11:56:35Z

2

If you want to be able to run range queries on dates, you need to map your field as a date field, otherwise it won't work as you expect. In the mapping you shared, createdDate is a string. You need to wipe your index and create a new one with the proper mapping for the createdDate field, like this:

curl -XPOST localhost:9200/documents -d '{
   "mappings": {
      "order": {
         "properties": {
             "createdDate": {
                "type": "date"
             }
         }
      }
   }
}'

Then you can reindex your data and your range query will work as expected.

answered Jan 27, 2016 at 11:56

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

M.ax Over a year ago

Thanks for your reply! I have a problem with converting field to date, because elastic service supported by external team and not only me use this. So, it may be not simple. I thought elastic can range string fields (elastic.co/guide/en/elasticsearch/guide/current/…) And it works in other fields, so why it's a problem in this field?

Val Over a year ago

The range query works for string fields but all it is going to do is simply perform a "lexicographical" range (i.e. alphabetical order), i.e. it will not interpret the date. If you ever want to be able to filter on date ranges using this field, it has to be a date field.

M.ax Over a year ago

And from point of view lexicographical range, string "2015-12-14" is not greater than string "2015-11-01"? I thought it compares strings char by char and "2015-1" is equals, but "2" greater than "1"

Val Over a year ago

The problem here is that your string is also analyzed, so the string 2015-12-14 20:28:23.557 will be tokenized as the six following tokens, i.e. 2015, 12, 14, 20, 28 and 23.557. Hence, the range doesn't run on the full string value but on those tokens.

M.ax Over a year ago

Ok, thank you again! I'll try to convert field to date type.

|

Collectives™ on Stack Overflow

Elasticsearch. Range query for strings with dashes

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related