Elasticsearch sort script can only access keyword field as String

Question

This is my query,Elasticsearch version is 7.10.0

{
    "query": {
      "bool": {
        "must": [
          {
            "match_all": {}
          }
        ]
      }
    },
    "sort": {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": "doc['my_field'].value - params.paramVal",
          "params": {
            "paramVal": 1
          }
        },
        "order": "asc"
      }
    },
    "profile": false
  }

which my_field is an Integer keyword field.But this query result in an error

  "reason": {
    "type": "script_exception",
    "reason": "runtime error",
    "script_stack": [
      "doc['my_field'].value - params.paramVal",
      "                              ^---- HERE"
    ],
    "script": "doc['my_field'].value - params.paramVal",
    "lang": "painless",
    "position": {
      "offset": 33,
      "start": 0,
      "end": 49
    },
    "caused_by": {
      "type": "class_cast_exception",
      "reason": "class_cast_exception: Cannot apply [-] operation to types [java.lang.String] and [java.lang.Integer]."
    }
  }

Now I'm using Integer.parseInt(doc['my_field'].value) to workaround.Any idea to use the int field value directly?Thanks.

Answer 1

If my_field is an "Integer keyword field", I assume that it is stored as a string. If that's the case, then doc['my_field'].value returns a string and Painless won't allow you to subtract an integer from a string. You need to parse that string, like this:

Integer.parseInt(doc['my_field'].value) - params.paramVal

If my_field is supposed to contain an integer value you should probably store it as an integer type instead of keyword, especially if you need to run range queries on that field.

UPDATE:

If you don't want or can't reindex and want to still keep your current index, another thing you can do is to add an integer sub-field and update your index in place. It goes like this:

# First add an integer sub-field to your mapping
PUT index/_mapping
{
   "properties": {
      "my_field": {
         "type": "keyword",
         "fields": {
            "numeric": {
               "type": "integer"
            }
         }
      }
   }
}

# then update your index in-place
POST index/_update_by_query

When the update is done, the new my_field.numeric field will have been populated using the existing data in the keyword field and you can use it in your Painless script like this:

doc['my_field.numeric'].value - params.paramVal