像MySQL一样查询ES，一看就会、爽歪歪

👉 这是一个或许对你有用的社群

🐱 一对一交流/面试小册/简历优化/求职解惑，欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料： 

• 《项目实战（视频）》：从书中学，往事上“练”
• 《互联网高频面试题》：面朝简历学习，春暖花开
• 《架构 x 系统设计》：摧枯拉朽，掌控面试高频场景题
• 《精进 Java 学习指南》：系统学习，互联网主流技术栈
• 《必读 Java 源码专栏》：知其然，知其所以然

来源：小羊架构

• 1.简介
• 2.准备环境
• 3.搞起
• 1. 分页limit
• 2. order by 排序
• 3. group by 分组
• 4. SUM 求和
• 5. where
• 4.总结
• 5.最后附上相关链接

ElasticSearch是现在最流行的搜索引擎了，查询快，性能好。可能唯一的缺点就是查询的语法Query DSL（Domain Specific Language）比较难记，今天分享一个直接用sql查询ES的方法。

1.简介

先简单介绍一下这个sql查询，因为社区一直反馈这个Query DSL 实在是太难用了。大家可以感受一下下面这个es的查询。

GET /my_index/_search
{
"query": {
    "bool": {
      "must": [
        { "match": { "title": "search" } },
        {
          "bool": {
            "should": [
              { "term": { "category": "books" } },
              { "term": { "category": "music" } }
            ]
          }
        }
      ],
      "filter": {
        "range": {
          "price": { "gte": 20, "lte": 100 }
        }
      }
    }
  },
"aggs": {
    "avg_price_per_category": {
      "terms": {
        "field": "category",
        "size": 10
      },
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        }
      }
    }
  }
}

这个查询使用了bool查询来组合多个条件，包括must、should和filter。同时也包含了聚合(aggs)来计算不同类别的平均价格。对于业务查询来讲，这个查询很普通。但是还是很难理解，特别是对于新手来讲，更难记了，很容易出错。

如果是mysql的查询，就是这么写

    
SELECT title, category, price 
FROM my_index 
WHERE (title = 'search'AND (category = 'books'ORcategory = 'music')) 
AND price >= 20AND price <= 100
GROUPBYcategory
ORDERBYAVG(price) DESC
LIMIT10

mysql 的查询就很简洁明了，看起来更舒服，后续维护也更方便。

既然都是查询，为啥不兼容一下mysql的语法呢，像很多工具现在都是兼容mysql的语法，比如说hive，starrocks，flink等等，原因就是因为mysql的用户多，社区活跃。

还有一个原因就是因为mysql的语法比较简单，容易理解。所以ElasticSearch 官方ElasticSearch 从 6.3.0 版本也开始支持 SQL 查询了，这就是一个喜大奔普的事情了，哈哈。

下面是官方的文档和介绍，大家可以看看

https://www.elastic.co/guide/en/elasticsearch/reference/8.9/sql-overview.html

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

• 项目地址：https://github.com/YunaiV/ruoyi-vue-pro
• 视频教程：https://doc.iocoder.cn/video/

2.准备环境

大家在ES官网下载一下ES 启动就可以了，注意的是ES 需要JDK环境，然后就是需要在6.3.0以上的版本。

https://www.elastic.co/cn/downloads/past-releases#elasticsearch

建议也下载一下kibana

我这边下载的是7.15.2版本

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

• 项目地址：https://github.com/YunaiV/yudao-cloud
• 视频教程：https://doc.iocoder.cn/video/

3.搞起

创建一个索引 my_index

PUT /my_index
{
  "mappings": {
    "properties": {
      "title": { "type": "text" },
      "category": { "type": "keyword" },
      "price": { "type": "float" }
    }
  }
}

插入一些数据

POST /my_index/_doc/1
{
"title": "ES学习手册",
"category": "books",
"price": 29.99
}

POST /my_index/_doc/2
{
"title": "on my way",
"category": "music",
"price": 13.57
}

POST /my_index/_doc/3
{
"title": "Kibana中文笔记",
"category": "books",
"price": 21.54
}

传统的查询所有

GET /my_index/_search
{
  
}

返回的是文档的格式

如果用sql 查询

POST /_sql?format=txt
{
  "query": "SELECT * FROM my_index"
}

返回的是类似数据库的表格形式，是不是写起来更舒服呢。

1. 分页limit

POST /_sql?format=txt
{
  "query": "SELECT * FROM my_index limit 1"
}

和mysql 一样没啥，很简单。

2. order by 排序

POST /_sql?format=txt
{
  "query": "SELECT * FROM my_index order by price desc"
}

3. group by 分组

POST /_sql?format=txt
{
  


    
"query": "SELECT category,count(1) FROM my_index group by category"
}

4. SUM 求和

POST /_sql?format=txt
{
  "query": "SELECT sum(price) FROM my_index"
}

5. where

POST /_sql?format=txt
{
  "query": "SELECT * FROM my_index where price = '13.57'"
}

看看是不是支持时间的转换的处理，插入一些数据

POST /my_index/_doc/4
{
"title": "JAVA编程思想",
"category": "books",
"price": 21.54,
"create_date":"2023-11-18T12:00:00.123"
}

POST /my_index/_doc/5
{
"title": "Mysql操作手册",
"category": "books",
"price": 21.54,
"create_date":"2023-11-17T07:00:00.123"
}

时间转换为 yyyy-mm-dd 格式

    
POST /_sql?format=txt
{"query": "SELECT title, DATETIME_FORMAT(create_date, 'YYYY-MM-dd') date from my_index where category= 'books'" }

时间加减

POST /_sql?format=txt
{"query": "SELECT date_add('hour', 8,create_date) date from my_index where category= 'books'" }

字符串拆分

POST /_sql?format=txt
{
  "query": "SELECT SUBSTRING(category, 1, 3) AS SubstringValue FROM my_index"
}

基本上mysql 能查的 es sql 也能查，以后查询ES 数据就很方便的，特别是对于做各种报表的查询。像这样

一般对于这种报表，返回的数据都是差不多json数组的格式。而对于es sql,查询起来很方便

{
    "data": "5",
    "axis": "总数"
},
{
    "data": "0",
    "axis": "待出库"
},
{
    "data": "0",
    "axis": "配送中"
},
{
    "data": "5",
    "axis": "已签收"
},
{
    "data": "0",
    


    
"axis": "交易完成"
},
{
    "data": "0",
    "axis": "已取消"
},
{
    "data": "5",
    "axis": "销售"
}

4.总结

ES SQL查询的优点还是很多的，值得学习。使用场景也很多

• 简单易学： ES SQL查询使用SQL语法，对于那些熟悉SQL语法的开发人员来说，学习ES SQL查询非常容易。
• 易于使用： ES SQL查询的语法简单，易于使用，尤其是对于那些不熟悉Query DSL语法的开发人员来说。
• 可读性强： ES SQL查询的语法结构清晰，易于阅读和理解。

5.最后附上相关链接

ES 官方下载

https://www.elastic.co/cn/downloads/past-releases#elasticsearch

ES sql文档：

https://www.elastic.co/guide/en/elasticsearch/reference/8.9/sql-overview.html