Nested time ranges and term in one filter

Hello :slight_smile:

My data:

  • shops
  • working hours
  • shops with each of their working hours

I have a query which returns shops from first day of the month to last day of the month including working hours.
For example:

January 1st
between 9-11 hours - shop1 - shop2 and shop 3 is available
between 11-13 hours - shop4 - shop2 - shop 5 available

January 2st
between 9-11 hours - shop4 - shop3 and shop 1 is available
between 11-13 hours - shop2 - shop1 - shop 5 available

and etc....

The problem:
How I can filter those shops that for example:
Shop 1 and 5 always won't be visible between 9-11 hours but will be visible during 11-13 hours.

I tried (I am pasting only a part of my code. Query and etc... stuff is fine. ):
However, this gives me either only range from 11 hours or range lower than 11 hours.

                        "filter" : {
                            "should": [
                                {
                                    "bool": {
                                        "must_not": [
                                            {
                                                "range": {
                                                    "shopStartHour": {
                                                        "lt": 11
                                                    }
                                                }
                                            },
                                            {
                                                "term": {
                                                    "shop.number": "1"
                                                }
                                            },
                                            {
                                                "term": {
                                                    "shop.number": "5"
                                                }
                                            }
                                        ]
                                    }
                                },
                                {
                                    "bool": {
                                        "must": [
                                            {
                                                "range": {
                                                    "shopStartHour": {
                                                        "gt": 11
                                                    }
                                                }
                                            },
                                            {
                                                "term": {
                                                      "shop.number": "1"
                                                }
                                            },
                                            {
                                                "term": {
                                                    "shop.number": "5"
                                                }
                                            }
                                        ]
                                    }
                                }
                            ]
                          }

Any help or knowledge about this would be appreciated. Thanks :slight_smile:

What is your mappings, example data and expected result?

At the moment result is like this :

                {
                    "shopStartTime": "2022-01-14T09:00:00Z",
                    "shopEndTime": "2022-01-14T11:00:00Z",
                    "shops": [
                        {
                            "shopId": "1",
                        },
                        {
                            "shopId": "5",
                        },
                        {
                            "shopId": "3",
                        }
                    ]
                },
                {
                    "shopStartTime": "2022-01-14T11:00:00Z",
                    "shopEndTime": "2022-01-14T13:00:00Z",
                    "shops": [
                        {
                            "shopId": "4",
                        },
                        {
                            "shopId": "6",
                        },
                        {
                            "shopId": "3",
                        }
                    ]
                }

What I am trying to achieve that for example shop 5 won't be visible between 9-11, but would be visible between 11-13.

To paste mapping of my data is really hard because it's corporate stuff I cannot paste it here.
Query:

I am taking every day of the month
2022-01-17
2022-01-18
Which is later divided to hours 
2022-01-17 : 09-11;11-13;13-15 and etc...

I am taking all availble shops with their working hours. 
shop 5 working hours from 9:00 - 21:00 MONDAY-SUNDAY
shop1 working hours from 8:00-19-00  MONDAY-SUNDAY and etc...

I am mapping those shops to those hours I mentioned above and getting part of the result pasted above as well.

I need to apply certain rules that some shops would be visible via different time ranges. 

So what I need is to manipulate range twice. If I use range lte 11 must not shop 5 I will always get all shops working hours till 11 o'clock. I don't know how to manipulate must/should query that I could write like if statement. :confused:

Example

if (shop.id = 5){
range gte 11 
}

if (shop.id = 3){
range lt 11
}

and etc....

Maybe I've got the halfway yet.

You don't need share the whole mappings, but critical part for reproduce the result. I can not answer without enough information to reproduce the result.

First of all, the document is per shop base or any other, such as per shop+hour_range?

In addition, please use 'elastic' words such as 'document',' field', 'date histogram aggregation'...etc.
'I'm taking', 'devided to hours' and "mapping xxx to" are difficult to understand.

You can use hierarchical boolean query based on Set theory, but it works on document basis.

1 Like

Let's say an example of a document could be like this.

{
    "_index": "some_index",
    "_type": "_doc",
    "_id": "111111-55555-6666-5555-66666",
    "_version": 1,
    "_seq_no": 1234,
    "_primary_term": 1,
    "found": true,
    "_source": {
        "shop": {
            "id": "1"
        },
        "date": "2022-02-21",
        "dayOfWeek": 1,
        "isWeekend": false,
        "shopStartTime": "2022-02-21T15:00:00+00:00",
        "shopStartTimeHour": 15,
        "shopStartTimeMinute": 0,
        "shopOpeningTimestamps": [
            "2022-02-21T07:45:00+00:00",
            "2022-02-18T08:45:00+00:00",
            ....................
        ],
        "shopClosingTimestamps": [
            "2022-02-21T18:00:00+00:00",
            "2022-02-18T16:00:00+00:00",
             ..............
        ],
   }
}

Where I can find this Set theory? :slight_smile: In ealstic documentation page?

Set theory is a general term. You have to translate if/else condition to sets expressed by boolean query.

One example is as follows. Maybe it depends what 'range gte 11' mean clearly. I translated it into "the start time is gte 11' of the day".
I added gte xxxxxT00:00:00 because StartTime for other day (eg. "2022-02-21T07:45:00+00:00") will hit by {"gte": "2022-01-14T00:00:00Z"}.

{
  "query":{
    "bool":{
      "filter":[
        {
          "range":{
            "shopStartTime":{
              "gte": "2022-01-14T00:00:00Z",
              "lte": "2022-01-14T11:00:00Z"
            },
            "shopEndTime":{
              "gte": "2022-01-14T09:00:00Z",
              "lte": "2022-01-15T00:00:00Z"
            }
          }
        },
        {
          "bool":{
            "should":[
              {
                "bool":{
                  "filter":[
                    {"term": {"shop.id"": "5"}},
                    {"range": {"shopStartTime":{
                      "gte": "2022-01-14T11:00:00Z",
                      "lte": "2022-01-15T00:00:00Z",
                    }}}
                  ]
                }
              }
            ],
            "minimum_should_match":1
          }
        }
      ]
    }
  }
}

If you need more help, please reorganize the document and query to the minimum to reproduce the problem, and share them which can be copy-and-paset to the Dev Tool.

gte 11 means shopStartTimeHour. I will try this. Thanks for helping.