es中将sql转换成dsl

1、背景

在es中，有些时候我们知道sql语句是怎么写的，但是如果我想通过dsl来实现，那么该如何处理呢？其中在es中提供了将sql转换成dsl的api，此处我们来简单使用下。

2、准备数据

2.1 mapping

PUT /index_translate
{
  "mappings": {
    "properties": {
      "month": {
        "type": "keyword"
      },
      "brand": {
        "type": "keyword"
      },
      "salesVolume": {
        "type": "integer"
      }
    }
  }
}

2.2 插入数据

PUT /index_translate/_bulk
{"index":{"_id":1}}
{"month":"2023-01","brand":"宝马","salesVolume":100}
{"index":{"_id":3}}
{"month":"2023-02","brand":"大众","salesVolume":80}
{"index":{"_id":4}}
{"month":"2023-02","brand":"宝马","salesVolume":20}

3、使用sql实现一个简单的group by 操作

1、sql

POST _sql?format=txt
{
  "query": """
  select count(*), brand from index_translate where salesVolume>=10  group by brand having count(*) > 1
  """
}

注意：并不是所有的sql语法都是支持的。

2、转换成dsl

POST _sql/translate
{
  "query":"select count(*), brand from index_translate where salesVolume>=10  group by brand having count(*) > 1"
}

3、运行结果

4、参考文档

1、https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html