Elastic search script sort text as Number

Question

I have an Elastic search field ID which is a Number but set in the index as a "text". I cannot change the index because of the huge volume of data to reload. I am writing a script to do this sorting but getting a "Bad_request" error.

Script script = new Script(ScriptType.INLINE, "painless", "ctx._source.ID.keyword", Collections.emptyMap());
ScriptSortBuilder.ScriptSortType sortType = ScriptSortBuilder.ScriptSortType.NUMBER;
builder.sort(SortBuilders.scriptSort(script, sortType).order(SortOrder.DESC));

searchRequest.source(builder);
response = restsearchClient.search(searchRequest, RequestOptions.DEFAULT);

I have tried the following idorcode values: doc['ID'], doc['ID.keyword'], ctx._source.ID, ctx._source.ID.keyword.

---

please advice!

Answer 1

If my understanding correct then you want to sort on number which is store as string and not as integer in Elasticsearch.

Below is sample Elasticsearch Query:

{
  "query": {
    "match_all": {}
  },
  "sort": {
    "_script": {
      "type": "Number",
      "order": "desc",
      "script": {
        "lang": "painless",
        "source": """
        String s = doc['ID.keyword'].value;
          int idvalue = Integer.parseInt(s);
          return idvalue;
        """
      }
    }
  }
}

Below is Java code:

Script script = new Script(ScriptType.INLINE, "painless", "String s = doc['ID.keyword'].value;int idvalue = Integer.parseInt(s);return idvalue;", Collections.emptyMap());
ScriptSortBuilder.ScriptSortType sortType = ScriptSortBuilder.ScriptSortType.NUMBER;
builder.sort(SortBuilders.scriptSort(script, sortType).order(SortOrder.DESC));