How to filter elastic search document based on overflowing hour range?

Elastic Stack Elasticsearch
1

I have a restaurant index which has a nested field that contains dayOfWeek, opening_hour and closing_hour hour_range(HH:mm:ss).

       "hours":   { "type": "nested" ,
                             "properties": {
                                "day_of_week": {
                                    "type": "short"
                                },
                                "opening_hour": {
                                    "type": "date",
                                    "format": "strict_hour_minute_second"
                                },
                                "closing_hour": {
                                    "type": "date",
                                    "format": "strict_hour_minute_second"
                                },
                                "restaurant_id": {
                                    "type": "integer"
                                }
                            }
                    },

My requirement is that a restaurant could open till the next day meaning that it is valid for one restaurant to have opening hour less than closing hour (e.g: 22:00:00 - 02:00:00)

Based on that requirement, I will not be able to use elastic search date range field type because of the above and hence have to have separate fields for opening and closing hour.

Now, given above mapping, how could I query whether or not certain hour range falls between opening and closing hour?

Example:

                            Opening Hour     Start Range         End Range              Closing Hour

1.Normal case                  03:00            04:00              09:00                  10:00

2.Overflowing opening hour     23:00            01:00              03:00                  10:00

3.Overflowing closing hour     03:00            04:00              22:00                  02:00

4.Overflowing range            22:00            23:00              03:00                  04:00

All above four cases are valid and document SHOULD be returned.

In case #1 and #4 I simply have to find openingHours that is less than equal startRange AND closing hours greater than equal endRange

Now case#2 and #3 would require me to compare either the openingHour and startRange or closingHour and endRange and modify the query accordingly. Both would require me to access the indexed value openingHour and closingHour.

I thought of applying the logic through script query. However, I realized that in filter context https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-filter-context.html

_source is not available, only doc values which prohibit us from accessing nested fields.

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.