ES-DSL 或 es-jdbc 语法查询

　Admin

已于 2023-06-28 09:07:50 修改

阅读量725

点赞数

文章标签： elasticsearch java 数据库

于 2023-06-27 18:13:10 首次发布

本文链接：https://blog.csdn.net/weixin_43618209/article/details/131422467

版权

文章展示了如何使用ES-jdbc的SQL查询语法来操作Elasticsearch索引，与传统数据库查询进行对比。查询涉及固定和动态条件，如payState、verificationState、ticketsCancelState、touristId以及verificationTime的时间范围，并讨论了Elasticsearch中时间格式化的限制，以及如何进行分组聚合。

摘要由CSDN通过智能技术生成

简述：类似于DB库查询的sql语法，只是需要使用的语法规则跟表名等同替换成 ES-jdbc的语法跟索引

例如：ES：索引 ticketing_order_details DB：表 t_order_details

ES：时间格式化只能单个格式化年，月，日，时，分，秒然后进行组合

YEAR(time)，MONTH_OF_YEAR(time) 等等

DB：有时间格式化跟format 函数

format 可以设置其他格式

ES-jdbc 的查询

查询条件为：

固定条件1： payState = 3

固定条件2： verificationState IN ( 1, 2 )

固定条件3： ticketsCancelState IN (0,1,3)

动态条件1： touristId = '123456789'

动态条件2：verificationTime >= '2023-06-28 00:00:00'

动态条件3：verificationTime <= '2023-06-28 23:59:59'

写法一：

POST /_sql?format=txt

{
"query":"SELECT COUNT( detailsId ) AS count,YEAR(verificationTime) AS year, MONTH_OF_YEAR(verificationTime) AS month, DAY_OF_MONTH(verificationTime) AS day,HOUR_OF_DAY(verificationTime) AS hour FROM ticketing_order_details WHERE payState = 3 AND verificationState IN ( 1, 2 ) AND ticketsCancelState IN (0,1,3) AND touristId = '123456789' AND verificationTime >= '2023-06-28 00:00:00' AND verificationTime <= '2023-06-28 23:59:59' GROUP BY YEAR(verificationTime), MONTH_OF_YEAR(verificationTime), DAY_OF_MONTH(verificationTime),HOUR_OF_DAY(verificationTime)"
}

写法二：

POST /_sql?format=txt

{
"query":"SELECT COUNT( detailsId ) AS count,YEAR(verificationTime) AS year, MONTH_OF_YEAR(verificationTime) AS month, DAY_OF_MONTH(verificationTime) AS day,HOUR_OF_DAY(verificationTime) AS hour FROM ticketing_order_details WHERE payState = 3 AND verificationState IN ( 1, 2 ) AND ticketsCancelState IN (0,1,3) AND touristId = ? AND verificationTime >= ? AND verificationTime <= ? GROUP BY YEAR(verificationTime), MONTH_OF_YEAR(verificationTime), DAY_OF_MONTH(verificationTime),HOUR_OF_DAY(verificationTime)",
"params":[123456789,"2023-06-28 00:00:00","2023-05-28 23:59:59"]
}