Canvas : Is there a way to use range bucket while querying data

[sujeet.ssv (Sujeet Velapure)]

Hi

I am novice and still learning how to use canvas feature. I would like to know how to derive bucket aggregation in canvas preferably with some example.
Something like below , how it can be written in essql?

GET jtlresults-cttd3/_search
{
"aggs" : {
"time_ranges" : {
"range" : {
"field" : "elapsed",
"keyed" : true,
"ranges" : [
{ "key" : "total", "from" :0 },
{ "key" : "low", "to" : 2000 },
{ "key" : "medium", "from" : 2000, "to" : 5000 },
{ "key" : "high", "from" : 5000 }
]
}
}
}
}

[flash1293 (Joe Reuter)]

Hi @sujeet.ssv,

yes this is possible by using the CASE statement as described here: https://www.elastic.co/guide/en/elasticsearch/reference/7.6/sql-functions-conditional.html#sql-functions-conditional-case-groupby-custom-buckets

SELECT count(*) AS count,
  CASE WHEN NVL(languages, 0) = 0 THEN 'zero'
    WHEN languages = 1 THEN 'one'
    WHEN languages = 2 THEN 'bilingual'
    WHEN languages = 3 THEN 'trilingual'
    ELSE 'multilingual'
  END as lang_skills
FROM employees
GROUP BY lang_skills
ORDER BY lang_skills;

Just put the ranges you want to see in the WHEN conditions