How to convert case at runtime at search query in kibana

[kanchan]

hi,

How we can achieve this in kibana:

select * from CEM_SOLR_REPORT where lower(PROGRESS_FLAG)='success';

[jbudz (Jon Budzenski)]

The equivalent would be creating a scripted field and then searching on it:

query:

LOWER_CASE_PROGRESS_FLAG: success

The "table" would be selected by the index pattern dropdown on the left.

[kanchan]

I am using like below, but it is giving error.

GET /hist_latest_5.5.0/_search
{
"query": {
"bool": {
"must": [
{
"script": {
"script": "doc['bank_client_entity_name'].value.toLowerCase()=SHELL TREASURY CENTRE LIMITED"
}
}
]
}
}

}

[jbudz (Jon Budzenski)]

A few small changes, does this work for you?

GET /hist_latest_5.5.0/_search
{
  "query": {
    "bool": {
      "must": [{
        "script": {
          "script": "doc['bank_client_entity_name'].value.toUpperCase() == 'SHELL TREASURY CENTRE LIMITED'"
        }
      }]
    }
  }
}

[kanchan]

Hi Jon,
I used this code like:
GET /hist_latest_5.5.0/_search
{
"query": {
"bool": {
"must": [{
"script": {
"script": "doc['bank_client_entity_name.keyword'].value.toUpperCase == 'SHELL TREASURY CENTRE LIMITED'"
}
}]
}
}
}

but it is giving exception like:

{
"error": {
"root_cause": [
{
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"doc['bank_client_entity_name.keyword'].value.toUpperCase == 'SHELL TREASURY CENTRE LIMITED'",
" ^---- HERE"
],
"script": "doc['bank_client_entity_name.keyword'].value.toUpperCase == 'SHELL TREASURY CENTRE LIMITED'",
"lang": "painless"
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query",
"grouped": true,
"failed_shards": [
{
"shard": 0,
"index": "hist_latest_5.5.0",
"node": "xKmSFoaSQwOhSjguRZI5pA",
"reason": {
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"doc['bank_client_entity_name.keyword'].value.toUpperCase == 'SHELL TREASURY CENTRE LIMITED'",
" ^---- HERE"
],
"script": "doc['bank_client_entity_name.keyword'].value.toUpperCase == 'SHELL TREASURY CENTRE LIMITED'",
"lang": "painless",
"caused_by": {
"type": "illegal_argument_exception",
"reason": "Unable to find dynamic field [toUpperCase] for class [java.lang.String]."
}
}
}
]
},
"status": 500
}

[kanchan]

Thanks jbudz!!!!!! its working f9. but I have to implement it in java with rest client.how we can achieve it.

my code in kibana is:

GET /hist_latest_5.5.0/_search
{
"query": {
"bool": {
"must": [{
"script": {
"script": "doc['bank_client_entity_name.keyword'].empty ? false : doc['bank_client_entity_name.keyword'].value.toLowerCase() == 'pravin mehta'"
}
}]
}
}
}

I have to do this at java side.