So I have a list of stores having multiple operational_hour that determines whether the store is open or closed. In one day a store can open and close multiple times. The operational_hour is nested so I can query based on whether it matches a whole object in operational_hour array.
Here is how the operational_hour looks like:
"operational_hour": [
{ // omitted some of the days to make the list shorter
"open_int": 0, // 0 represents the time a store opens in seconds
"close_int": 49440,
"week_day": "Friday" // day it opens, notice multiple Friday below
},
{
"open_int": 57600,
"close_int": 68400,
"week_day": "Friday"
},
{
"open_int": 77760,
"close_int": 82800,
"week_day": "Friday"
},
{
"open_int": 0,
"close_int": 86340,
"week_day": "Saturday"
}
],
I can query when a store is open in certain time of certain day:
{
"from": 0,
"query": {
"bool": {
"must": [
{
"nested": {
"path": "operational_hour",
"query": {
"bool": {
"must": [
{
"range": {
"operational_hour.open_int": {
"from": null,
"include_lower": true,
"include_upper": true,
"to": 5280 // current time in seconds
}
}
},
{
"range": {
"operational_hour.close_int": {
"from": 5280,
"include_lower": true,
"include_upper": true,
"to": null
}
}
},
{
"term": {
"operational_hour.week_day.keyword": "Friday"
}
}
]
}
}
}
}
]
}
},
"size": 100
}
However, I'm having problems when querying the time when the store is closed.
I tried using must_not within must, thinking it must query the open time, then invert the result, but it didn't work this way. For a day with more than one operational_hour, the result will find that one of the operational_hour matches that it is not open anyway, so it shows that result.
{
"from": 0,
"query": {
"bool": {
"must": [
{
"nested": {
"path": "operational_hour",
"query": {
"bool": {
"must_not": [
{
"bool": {
"must": [
{
"range": {
"operational_hour.open_int": {
"from": null,
"include_lower": true,
"include_upper": true,
"to": 79200
}
}
},
{
"range": {
"operational_hour.close_int": {
"from": 79200,
"include_lower": true,
"include_upper": true,
"to": null
}
}
}
]
}
},
{
"term": {
"operational_hour.week_day.keyword": "Friday"
}
}
]
}
}
}
}
]
}
},
"size": 100
}
Edit: This is what the stores table look like:
"_source": {
"id": 39,
"name": "Some Store Name",
"operational_hour": [
{
"open": "00:00:00",
"open_int": 0,
"close": "23:59:00",
"close_int": 86340,
"week_day": "Sunday",
"week_day_int": 0
},
{
"open": "00:00:00",
"open_int": 0,
"close": "23:59:00",
"close_int": 86340,
"week_day": "Monday",
"week_day_int": 1
},
{
"open": "00:00:00",
"open_int": 0,
"close": "23:59:00",
"close_int": 86340,
"week_day": "Tuesday",
"week_day_int": 2
},
{
"open": "00:00:00",
"open_int": 0,
"close": "00:00:00",
"close_int": 0,
"week_day": "Wednesday",
"week_day_int": 3
},
{
"open": "00:00:00",
"open_int": 0,
"close": "14:59:00",
"close_int": 53940,
"week_day": "Thursday",
"week_day_int": 4
},
{
"open": "23:00:00",
"open_int": 82800,
"close": "23:20:00",
"close_int": 84000,
"week_day": "Thursday",
"week_day_int": 4
},
{
"open": "23:24:00",
"open_int": 84240,
"close": "23:57:00",
"close_int": 86220,
"week_day": "Thursday",
"week_day_int": 4
},
{
"open": "00:00:00",
"open_int": 0,
"close": "13:44:00",
"close_int": 49440,
"week_day": "Friday",
"week_day_int": 5
},
{
"open": "16:00:00",
"open_int": 57600,
"close": "19:00:00",
"close_int": 68400,
"week_day": "Friday",
"week_day_int": 5
},
{
"open": "21:36:00",
"open_int": 77760,
"close": "23:00:00",
"close_int": 82800,
"week_day": "Friday",
"week_day_int": 5
},
{
"open": "00:00:00",
"open_int": 0,
"close": "23:59:00",
"close_int": 86340,
"week_day": "Saturday",
"week_day_int": 6
}
],
"coordinate": {
"lat": -16.2213163,
"lon": 16.81944
}
},
What I want is to query closed stores based on day and time.
This is Elastic 7.x.
Any hints?
question from:
https://stackoverflow.com/questions/65850981/elastic-querying-if-not-within-certain-range 
