ElasticSearch-Filter by date range when the date column field type is text/keyword in index

Question

I'm a newbie to ElasticSearch and looking for a help. ElasticSearch version is "7.3.2".

We've an index with a startDate column is defined as keyword type. Due to which when we write date query like below, there is lexical comparison happening and query results are not as expected.

Below column definition:

"createTime": {
  "type": "text",
  "fields": {
    "keyword": {
      "type": "keyword",
      "ignore_above": 256
    }
  },
  "fielddata": true
},

The data gets stored in the format of "createTime":"2023-09-05T01:36:49.771245"

The query I've tried is something like the below:

{
  "sort": {
    "createTime": "desc"
  },
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "createTime": {
              "gte": "2023-09-05T01:36:44.896855100",
              "lte": "2023-09-05T01:37:29.503598900",
              "format": "yyyyMMdd'T'HHmmss.SSS"
            }
          }
        }
      ]
    }
  }
}

I also have looked at some of the similar questions. It looks like there is a limitation to altering the data type to date and we would need to have a parallel multi-field option.

Filter by date range if the date column field type is text/keyword

This reference is 3 years old. So, looking for any new alternative ways to solve my issue.

There were some functions like to_date in SQL which generally solves this kind of issues in RDBMS. Are there any similar ways of doing this in ElasticSearch??

I also have looked at some of the similar questions. It looks like there is a limitation to altering the data type to date and we would need to have a parallel multi-field option

Answer 1

The short answer is you can't, keyword data type can't be filtered as a data time in the range operator.

If you need to fix it you can reindex the data and alter the mapping before indexing again

first reindex your data to a temporal index

    POST _reindex
    {
      "source": {
        "index": "your-index"
      },
      "dest": {
        "index": "your-index-temp"
      }
    }

when the reindex is donde (check the doc count of the two index must be the same) delete the original index

    DELETE your-index

Now Apply the new mapping

    PUT /your-index/_mapping
    {
      "properties": {
        "mi-campo": {
          "type": "date",
          "format": "yyyyMMdd'T'HHmmss.SSS"
        },
        // Other fields if you need to map
      }
    }

and reindex again the that form the temp index

    POST _reindex
    {
      "source": {
        "index": "your-index-temp"
      },
      "dest": {
        "index": "your-index"
      }
    }