Filter search results by hours working with elasticsearch

Question

I use elasticsearch to index and find locations, and I am having a specific problem with filtering by hours of work, which I don’t know how to work

In principle, each place will have a working hour (for each day of the week), and each day can have more than 1 "set" of working hours (now we use 2).

For example: Monday: open 9am / close 12pm open 1pm/close 9pm

Given the current time and current day of the week, I need to find "open" locations.

I don’t know how I should index this working hour along with location data and how to use them to filter the results, any help, suggestion would be really appreciated

Hello

Answer 1

The best way to do this is to use nested documents.

First: set up your mapping to indicate that the hours document should be treated as nested:

 curl -XPUT 'http://127.0.0.1:9200/foo/?pretty=1' -d ' { "mappings" : { "location" : { "properties" : { "hours" : { "include_in_root" : 1, "type" : "nested", "properties" : { "open" : { "type" : "short" }, "close" : { "type" : "short" }, "day" : { "index" : "not_analyzed", "type" : "string" } } }, "name" : { "type" : "string" } } } } } ' 

Add some data: (pay attention to several values ​​for hours of operation)

 curl -XPOST 'http://127.0.0.1:9200/foo/location?pretty=1' -d ' { "name" : "Test", "hours" : [ { "open" : 9, "close" : 12, "day" : "monday" }, { "open" : 13, "close" : 17, "day" : "monday" } ] } ' 

Then run your query, filtering the current day and time:

 curl -XGET 'http://127.0.0.1:9200/foo/location/_search?pretty=1' -d ' { "query" : { "filtered" : { "query" : { "text" : { "name" : "test" } }, "filter" : { "nested" : { "path" : "hours", "filter" : { "and" : [ { "term" : { "hours.day" : "monday" } }, { "range" : { "hours.close" : { "gte" : 10 } } }, { "range" : { "hours.open" : { "lte" : 10 } } } ] } } } } } } ' 

That should work.

Unfortunately, at 0.17.5 it throws NPE - this is most likely a simple bug that will be fixed in the near future. I discovered a problem for this: https://github.com/elasticsearch/elasticsearch/issues/1263

UPDATE Unusually, now I can not replicate NPE - this query works correctly both in version 0.17.5 and higher. There must have been a temporary glitch.

Clint

Answer 2

The above solution does not work, because if you have something open 2-4 on Monday and 6-8 on Tuesday, then filtering on Monday at 6 will return the document. The following is an example of a pseudo-son to illustrate how this should be done.

 { "business_document": "...", "hours": { "1": [ { "open": 930, "close": 1330 }, { "open": 1530, "close": 2130 } ], "2": [ { "open": 1000, "close": 2100 } ], "3": [ { "open": 1000, "close": 2100 } ], "4": [ { "open": 1000, "close": 2100 } ], "5": [ { "open": 1000, "close": 2100 } ], "6": [ { "open": 1000, "close": 2100 } ], "7": [ { "open": 930, "close": 1330 }, { "open": 1530, "close": 2130 } ] } } Sample Filter (can be applied to any query for a businesses): { "filter": { "and": [ //Must match all following clauses { "range": { "hours.1.open": { //Close Hour of Day 1 (current day) "lte": 1343 //Store open time is less than 13:43 (current time) } } }, { "range": { "hours.1.close": { //Close Hour of Day 1 (current day) "gte": 1343 //Store close time is greater than 13:43 (current time) } } } ] } } 

All times must be in 24-hour format using standard time zone (GMT)

Answer 3

The easiest way to do this is by naming and indexing time slots when the location is open. First, you need to come up with a scheme that assigns a name for each time interval when a location can be opened. For example, thu17 may represent 5PM on Thursday. Then the location in your example should be indexed by several fields "open" containing the following values: mon09, mon10, mon11, mon13, mon14, mon15, mon16, mon17, mon18, mon19, mon20, tue09, tue10, etc. To show only the locations that were open on Thursday 7AM, you just need to add this filter to your query: open: thu07.

You do not need to use this specific naming scheme. For example, you can simply count the number of hours since the beginning of the week. In this case, 9 AM on Monday will be 9, 11 PM on Monday - 23, 2 AM on Tuesday - 26, etc.