# Nested time ranges and term in one filter **URL:** \ **Category:** Elasticsearch\ **Created:** [January 14, 2022, 9:36am UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392 "2022-01-14T09:36:19Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![D1sturbance](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/d1sturbance/32/98965_2.png) [@D1sturbance](https://discuss.elastic.co/u/D1sturbance)\ **Post date:** [January 14, 2022, 9:36am UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/1 "2022-01-14T09:36:19Z")
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. ```auto "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" } } ] } } ] } ``` ---
**Author:** ![D1sturbance](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/d1sturbance/32/98965_2.png) [@D1sturbance](https://discuss.elastic.co/u/D1sturbance)\ **Post date:** [January 17, 2022, 11:00am UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/2 "2022-01-17T11:00:55Z")
Any help or knowledge about this would be appreciated. Thanks 🙂 ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 17, 2022, 1:51pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/3 "2022-01-17T13:51:20Z")
What is your mappings, example data and expected result? ---
**Author:** ![D1sturbance](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/d1sturbance/32/98965_2.png) [@D1sturbance](https://discuss.elastic.co/u/D1sturbance)\ **Post date:** [January 17, 2022, 3:19pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/4 "2022-01-17T15:19:00Z")
At the moment result is like this : ```auto { "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: ```auto 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 ```auto if (shop.id = 5){ range gte 11 } if (shop.id = 3){ range lt 11 } and etc.... ``` ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 17, 2022, 4:34pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/5 "2022-01-17T16:34:32Z")
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. > [@D1sturbance](#): > > I don't know how to manipulate must/should query You can use hierarchical boolean query based on Set theory, but it works on document basis. ---
**Author:** ![D1sturbance](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/d1sturbance/32/98965_2.png) [@D1sturbance](https://discuss.elastic.co/u/D1sturbance)\ **Post date:** [January 18, 2022, 12:04pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/6 "2022-01-18T12:04:52Z")
Let's say an example of a document could be like this. ```auto { "_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", .............. ], } } ``` ---
**Author:** ![D1sturbance](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/d1sturbance/32/98965_2.png) [@D1sturbance](https://discuss.elastic.co/u/D1sturbance)\ **Post date:** [January 18, 2022, 12:15pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/7 "2022-01-18T12:15:19Z")
> [@Tomo\_M](#): > > You can use hierarchical boolean query based on Set theory, but it works on document basis. Where I can find this Set theory? 🙂 In ealstic documentation page? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 18, 2022, 1:59pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/8 "2022-01-18T13:59:41Z")
[Set theory](https://en.wikipedia.org/wiki/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"}. ```auto { "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 } } ] } } } ``` ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 18, 2022, 2:02pm UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/9 "2022-01-18T14:02:16Z")
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. ---
**Author:** ![D1sturbance](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/d1sturbance/32/98965_2.png) [@D1sturbance](https://discuss.elastic.co/u/D1sturbance)\ **Post date:** [January 19, 2022, 9:42am UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/10 "2022-01-19T09:42:20Z")
gte 11 means shopStartTimeHour. I will try this. Thanks for helping. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 16, 2022, 9:43am UTC](https://discuss.elastic.co/t/nested-time-ranges-and-term-in-one-filter/294392/11 "2022-02-16T09:43:02Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.