Elasticsearch Painless script search document based on availability

Question

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?

Answer 1

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
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
}