前言
今天遇到一个问题,es里有个date字段,存储的是2020-12-28T16:16:22.000Z
日期+时间,现在有个查询任意时段内的数据,不考虑日期,例如,查询08:10:00
到18:00:00
之间的数据。这种情况下,可以使用script
进行查询或过滤。
正文
官方文档地址:https://www.elastic.co/guide/en/elasticsearch/reference/7.10/query-dsl-script-query.html
查询语句:
{
"query": {
"bool": {
"must": [
{
"script": {
"script": "doc['date'].value.get(ChronoField.SECOND_OF_DAY) > 10 && doc['date'].value.get(ChronoField.SECOND_OF_DAY) < 86399 "
}
}
]
}
}
}
上面只是示例,这里使用SECOND_OF_DAY,是因为我这里不仅仅是小时,还包括秒。如果仅仅是小时范围内的,可以使用getHour()
。
7.0版本对方法进行了替换,可以查看变更里的说明:
getDayOfWeek() will be an enum instead of an int, if you need to use an int, use getDayOfWeekEnum().getValue()
getMillis() should be replaced with toInstant().toEpochMilli()
getCenturyOfEra() should be replaced with get(ChronoField.YEAR_OF_ERA) / 100
getEra() should be replaced with get(ChronoField.ERA)
getHourOfDay() should be replaced with getHour()
getMillisOfDay() should be replaced with get(ChronoField.MILLI_OF_DAY)
getMillisOfSecond() should be replaced with get(ChronoField.MILLI_OF_SECOND)
getMinuteOfDay() should be replaced with get(ChronoField.MINUTE_OF_DAY)
getMinuteOfHour() should be replaced with getMinute()
getMonthOfYear() should be replaced with getMonthValue()
getSecondOfDay() should be replaced with get(ChronoField.SECOND_OF_DAY)
getSecondOfMinute() should be replaced with getSecond()
getWeekOfWeekyear() should be replaced with get(WeekFields.ISO.weekOfWeekBasedYear())
getWeekyear() should be replaced with get(WeekFields.ISO.weekBasedYear())
getYearOfCentury() should be replaced with get(ChronoField.YEAR_OF_ERA) % 100
getYearOfEra() should be replaced with get(ChronoField.YEAR_OF_ERA)
toString(String) should be replaced with a DateTimeFormatter
toString(String,Locale) should be replaced with a DateTimeFormatter
当然上面的写法有一个问题,因为es对脚本会进行编译并存储在缓存中,上面我们把变量写死为10,86399,会导致下一次传递新的参数时,es会重新编译并存储。
如果短时间内编译多次,es会拒绝并向外抛错。对于大多数上下文,默认情况下每5分钟最多可以编译15个脚本。对于ingest 上下文,默认脚本编译速率是无限的。这个设置可以修改。
更好的办法是我们对变量通过参数传入,这里以小时过滤为例吧,例如:
{
"query": {
"bool": {
"filter": {
"script": {
"script": {
"source": "doc['date'].value.getHour() > params.begin && doc['date'].value.getHour() < params.end ",
"params": {
"begin":1,
"end": 18
}
}
}
}
}
}
}
还有一种方式是,先保存这个脚本,然后通过id调用脚本
# 保存脚本
POST _scripts/mytest-script
{
"script": {
"lang": "painless",
"source": "doc['date'].value.getHour() > params.begin && doc['date'].value.getHour() < params.end "
}
}
# 查询
{
"query": {
"bool": {
"filter": {
"script": {
"script": {
"id": "mytest-script",
"params": {
"begin": 5,
"end": 18
}
}
}
}
}
}
}
# 暂时不知道如何查询所有已存在脚本。。。
# 查看脚本内容
GET _scripts/mytest-script
# 删除脚本
DELETE _scripts/mytest-script