I'm using ES version 7.0. I have a index of stores with availability (opening and closing time) in UTC time. I stored time in Integer so that it can be easy to match with the current time in painless script.
One sample doc is below:
{
"availability" : {
"thu" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
],
"tue" : [
{
"start" : 1300,
"end" : 2400
},
{
"start" : 0,
"end" : 400
}
],
"wed" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
],
"sat" : [
{
"start" : 1400,
"end" : 2400
},
{
"start" : 0,
"end" : 500
}
],
"fri" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
],
"mon" : [
{
"start" : 0,
"end" : 200
},
{
"start" : 1300,
"end" : 2400
}
],
"sun" : [
{
"start" : 1400,
"end" : 2400
},
{
"start" : 0,
"end" : 200
}
]
},
.
.
.
.
}
below is the query with painless script:
GET stores/_search
{
"query": {
"bool": {
"filter" : {
"script" : {
"script" : {
"source": "String d = params.day, start_key = 'availability.'+d+'.start', end_key = 'availability.'+d+'.end'; long t = params.time; if(doc[start_key].size() != 0 && doc[end_key].size() != 0){ long s = doc[start_key].value; long e = doc[end_key].value; return (s <= t && e > t); }",
"lang": "painless",
"params" : {
"day" : "wed",
"time" : 300
}
}
}
}
}
}
}
The above query is working for time 300 for Wednesday and giving the above doc in result but not working for time 1400 on Wednesday. Looks like script is matching always the first value from the availability array.
I also tried to loop through the day of availability but that is giving me no field found error.
GET stores/_search
{
"query": {
"bool": {
"filter" : {
"script" : {
"script" : {
"source": "String d = params.day, start_key = 'availability.'+d+'.start', end_key = 'availability.'+d+'.end'; long t = params.time; if(doc[start_key].size() != 0 && doc[start_key].size() != 0){ for(item in doc['availability.'+d]){ long s = item['start'].value; long e = item['end'].value; if (t >= s && t < e){ return true; } }}",
"lang": "painless",
"params" : {
"day" : "wed",
"time" : 300
}
}
}
}
}
}
}
The above query returns the below error
{ ....
"reason": {
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:94)",
"org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:41)",
"for(item in doc['availability.'+d]){ long ",
" ^---- HERE"
],
"script": "String d = params.day, start_key = 'availability.'+d+'.start', end_key = 'availability.'+d+'.end'; long t = params.time; if(doc[start_key].size() != 0 && doc[start_key].size() != 0){ for(item in doc['availability.'+d]){ long s = item['start'].value; long e = item['end'].value; if (t >= s && t < e){ return true; } }}",
"lang": "painless",
"caused_by": {
"type": "illegal_argument_exception",
"reason": "No field found for [availability.wed] in mapping with types []"
}
}
..... }
Also getting error while using doc['availability']['wed']
Anything that I'm missing here?
If availability.wed is of type object use below
{
"query": {
"script": {
"script": {
"source": "String d = params.day; for(int i=0; i<doc['availability.'+params.day+'.start'].length;i++){ long start =doc['availability.'+params.day+'.start'][i]; long end = doc['availability.'+params.day+'.end'][i]; if(start <= params.time && end > params.time){ return true;}} ",
"lang": "painless",
"params": {
"day": "wed",
"time": 2300
}
}
}
}
}
If availability.wed is of type nested use below
Mapping:
PUT testindex10/_mappings
{
"properties": {
"name":{
"type": "text"
},
"availability": {
"type": "object",
"properties": {
"mon": {
"type": "nested",
"properties": {
"start": {
"type": "integer"
}
}
},
"tue": {
"type": "nested",
"properties": {
"start": {
"type": "integer"
}
}
},
"wed": {
"type": "nested",
"properties": {
"start": {
"type": "integer"
}
}
},
"thu": {
"type": "nested",
"properties": {
"start": {
"type": "integer"
}
}
},
"fri": {
"type": "nested",
"properties": {
"start": {
"type": "integer"
}
}
},
"sat": {
"type": "nested",
"properties": {
"start": {
"type": "integer"
}
}
}
}
}
}
}
Data:
[
{
"_index" : "testindex10",
"_type" : "_doc",
"_id" : "snyiPm0ButCCF6l_WyTl",
"_score" : 1.0,
"_source" : {
"name" : "store1",
"availability" : {
"mon" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
],
"tue" : [
{
"start" : 1300,
"end" : 2400
},
{
"start" : 0,
"end" : 400
}
],
"wed" : [
{
"start" : 0,
"end" : 200
},
{
"start" : 1300,
"end" : 2400
}
],
"thu" : [
{
"start" : 1400,
"end" : 2400
},
{
"start" : 0,
"end" : 500
}
],
"fri" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
]
}
}
},
{
"_index" : "testindex10",
"_type" : "_doc",
"_id" : "s3yiPm0ButCCF6l_liQq",
"_score" : 1.0,
"_source" : {
"name" : "store2",
"availability" : {
"mon" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
],
"tue" : [
{
"start" : 1300,
"end" : 2400
},
{
"start" : 0,
"end" : 400
}
],
"wed" : [
{
"start" : 0,
"end" : 500
},
{
"start" : 1300,
"end" : 2400
}
],
"thu" : [
{
"start" : 1400,
"end" : 2400
},
{
"start" : 0,
"end" : 500
}
],
"fri" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
]
}
}
}
]
Query
GET testindex10/_search
{
"query": {
"bool": {
"filter": {
"nested": {
"path": "availability.wed",
"query": {
"script": {
"script": {
"source": "String d = params.day; long start =doc['availability.'+params.day+'.start'].value; long end = doc['availability.'+params.day+'.end'].value; if(start <= params.time && end > params.time){ return true;} ",
"lang": "painless",
"params": {
"day": "wed",
"time": 400
}
}
}
}
}
}
}
}
}
Result:
[
{
"_index" : "testindex10",
"_type" : "_doc",
"_id" : "s3yiPm0ButCCF6l_liQq",
"_score" : 0.0,
"_source" : {
"name" : "store2",
"availability" : {
"mon" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
],
"tue" : [
{
"start" : 1300,
"end" : 2400
},
{
"start" : 0,
"end" : 400
}
],
"wed" : [
{
"start" : 0,
"end" : 500
},
{
"start" : 1300,
"end" : 2400
}
],
"thu" : [
{
"start" : 1400,
"end" : 2400
},
{
"start" : 0,
"end" : 500
}
],
"fri" : [
{
"start" : 0,
"end" : 400
},
{
"start" : 1300,
"end" : 2400
}
]
}
}
}
]
Another way to solve same without script(better performance) would be
{
"query": {
"bool": {
"filter": {
"nested": {
"path": "availability.wed",
"query": {
"bool": {
"must": [
{
"range": {
"availability.wed.start": {
"lte": 400
}
}
},
{
"range": {
"availability.wed.end": {
"gte": 400
}
}
}
]
}
}
}
}
}
}
}