Elastic SQL: How to get length of a field?

SQL query fails when I do a String function

The original data is

  SELECT "message" FROM ".kibana-event-log-7.9.1-000001"
     message     
-----------------
eventLog starting
eventLog starting

When I do a length

POST /_sql?format=txt
{
  "query": """
  SELECT LENGTH("message") FROM ".kibana-event-log-7.9.1-000001"
  """
} 

The error shown is

"reason" : "Found 1 problem\nline 2:10: [LENGTH(\"message\")] cannot operate on field of data type [text]: 
No keyword/multi-field defined exact matches for [message]; define one or use MATCH/QUERY instead"

if I put as message.keyword, it fails completely by saying there is no such field present.

  SELECT LENGTH("message.keyword") FROM ".kibana-event-log-7.9.1-000001"

        "reason" : "Found 1 problem\nline 2:17: Unknown column [message.keyword]"

May be you need to update your index mapping to add the keywork field, as SQL function LENGTH can be only applied to keyword field

PUT .kibana-event-log-7.9.1-000001/_mapping
{
  "properties": {
    "message": {
      "type": "text",
       "norms" : false,
      "fields": {
        "keyword": {
          "type": "keyword"
        }
      }
    }
  }
}

I thought Elastic did keyword by default for all string fields? i.e. there will be a "message" and "message.keyword" automatically. may be i'm wrong

There is a mapping template behind
Check it here GET _template/.kibana-event-log-7.9.1-template
You will see this part, wish show that there is no keyword field

"message" : {
  "norms" : false,
  "type" : "text"
},

If there is no explicit mapping defined, Yes elasticsearch will automtically create 2 fields message as text and message.keyword as keyword

1 Like

correct. Mine shows as

        "message" : {
          "norms" : false,
          "type" : "text"
        },

Since, no explicity mapping not defined, should ES have created keyword field also?

This an explicit mapping that was defined, so no keyword will added by default
You should change the template .kibana-event-log-7.9.1-template
and also change the mapping of already created indexes like .kibana-event-log-7.9.1-000001