Search code examples
sqlelasticsearchtype-conversionkibana

Kibana : string to int issue using Elasticsearch SQL

I've got an ES index with a specific field, displayed by index mapping in Kibana's Console dev as :

    "spotted_field" : {
      "type" : "text"
    }

This field contains strings with only integers and I can't change that, so I was wondering how does perform a simple convertion string to int in order to calculate the whole sum as a dashboard metric.

I thought that using ElasticSearch SQL in a Canvas template could do the job. In the Data console, I began with a simple :

SELECT CONVERT("spotted_field", SQL_INTEGER) AS sum
FROM "my_index"

This one seemed to work, because everything looked fine in Preview Data, so next I tried :

SELECT SUM(CONVERT("spotted_field", SQL_INTEGER)) AS sum
FROM "my_index"

But got this reported error in Preview data :

[essql] > Unexpected error from Elasticsearch: search_phase_execution_exception - all shards failed

This message could be related to a format issue... So what did I miss for the conversion ? Can the conversion work better if the field contains keywords instead of strings ?

PS : I'm using v 7.17.12 version of Kibana

EDIT 1 : I'm sure that function CONVERT worked and return the expected format.

Solution

  • Long story short : conversion will work on a string's field (it it has only numbers), but for operations like sums it must be formated as keywords.