Hello,
I have an mapping for a field visit_times
which is a date
type and contains many epoch timestamps. I have some complex queries such as checking the dayOfWeek of any of the values within that array or even checking just within timestamps.
Here is an example query which takes really long:
POST /programs/_search
{
"query": {
"bool": {
"must": [
{
"script": {
"script": {
"lang": "painless",
"source": """
for (visit_time in doc['visit_times']) {
long milliseconds = visit_time.toInstant().toEpochMilli();
if (milliseconds >= Long.parseLong("1582588800000") && milliseconds <= Long.parseLong("1582675199999")) {
return true;
}
}
return false;
"""
}
}
}
]
}
}
}
OR this query:
POST /programs/_search
{
"query": {
"bool": {
"must": [
{
"term": {
"program_id": 155
}
},
{
"script": {
"script": {
"lang": "painless",
"source": """
for (date_field in doc['last_waitlist_time']) {
int hourOfDay = date_field.getHour();
int minuteOfHour = date_field.getMinuteOfHour();
if (hourOfDay < Integer.parseInt("23") && minuteOfHour < Integer.parseInt("00")) {
return true;
}
}
return false;
"""
}
}
}
]
}
}
}
Mapping for that field:
"visit_times" : {
"type" : "date",
"null_value" : "0"
},
This takes quite a bit of time compared to other queries. Is there another way to do this? It's really quick using expression however that doesn't loop through the whole array, instead it finds the minimum. IIRC.