Nested time ranges and term in one filter

D1sturbance · January 14, 2022, 9:36am

Hello

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

D1sturbance · January 17, 2022, 11:00am

Any help or knowledge about this would be appreciated. Thanks

Tomo_M · January 17, 2022, 1:51pm

What is your mappings, example data and expected result?

D1sturbance · January 17, 2022, 3:19pm

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.

Example

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

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

and etc....

Tomo_M · January 17, 2022, 4:34pm

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.

D1sturbance · January 18, 2022, 12:04pm

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",
             ..............
        ],
   }
}

D1sturbance · January 18, 2022, 12:15pm

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

Tomo_M · January 18, 2022, 1:59pm

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

Tomo_M · January 18, 2022, 2:02pm

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.

D1sturbance · January 19, 2022, 9:42am

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

system · February 16, 2022, 9:43am

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

Topic		Replies	Views
Elastisearch daterange filter - days of week Elasticsearch	1	456	December 15, 2020
Date/time filter on nested items Elasticsearch	1	316	July 6, 2017
Trying to do a simple query filter Elasticsearch	6	1038	April 2, 2019
Filtering by Range Elasticsearch	5	6709	January 26, 2018
Filtering by date and time as different fields Elasticsearch	10	11998	July 5, 2017

Nested time ranges and term in one filter

Related topics