_sql/translate api translate incorect case when field

Elastic Stack Elasticsearch
1

I have a query like this and although /_sql endpoint return correct results. /_sql/translate only return fields that use in query, without calculation

POST /_sql/translate
{
  "query": "SELECT Broker,IssueCount,Splits,CASE  WHEN LCASE(Broker) LIKE '%kgh%' THEN IssueCount/2 ELSE Splits END AS TungTran,DATEDIFF('hour', S3_3, Created) AS DateDiff FROM projects_dev",
  "fetch_size": 10
}

result from translate api:

{
  "size": 10,
  "_source": false,
  "fields": [
    {
      "field": "Broker"
    },
    {
      "field": "IssueCount"
    },
    {
      "field": "Splits"
    },
    {
      "field": "S3_3",
      "format": "strict_date_optional_time_nanos"
    },
    {
      "field": "Created",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

I expected case when field will be convert in to some painless script or something else. Please give any suggestion.

2

Hi @tung238

Maybe it's some limitation of the SQL Translate API. If you want to write the script I suggest this doc: How to write scripts | Elasticsearch Guide [8.17] | Elastic