# Nested integer\_range mappings/query? **URL:** \ **Category:** Elasticsearch\ **Created:** [May 24, 2018, 7:22am UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085 "2018-05-24T07:22:12Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![panthersquirrel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panthersquirrel/32/31548_2.png) [@panthersquirrel](https://discuss.elastic.co/u/panthersquirrel)\ **Post date:** [May 24, 2018, 7:22am UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/1 "2018-05-24T07:22:12Z")
Suppose i want this structure for a user document: ```auto /users/_doc/1 { hours: { open: [ { gte: 10, lt: 20 }, { gte: 30, lt: 40 } ... ], closed: [ { gte: 50, lt: 60 }, { gte: 70, lt: 80 } ... ] } } ``` 1. What's this document's required mapping? 2. If I want to find all users where hours.open.contains(15) AND NOT hours.closed.contains(20), what would this query look like? ---
**Author:** ![RahulD](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@RahulD](https://discuss.elastic.co/u/RahulD)\ **Post date:** [May 24, 2018, 5:43pm UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/2 "2018-05-24T17:43:44Z")
The hours index will have 2 separate nested fields; open and close The open and close nested fields will have 2 fields inside each one: gte and lt of type integer About the query: do you want 15 to be between gte and lt for the open field and 20 to NOT be between gte and lt for close field? ---
**Author:** ![panthersquirrel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panthersquirrel/32/31548_2.png) [@panthersquirrel](https://discuss.elastic.co/u/panthersquirrel)\ **Post date:** [May 24, 2018, 9:08pm UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/3 "2018-05-24T21:08:55Z")
> About the query: do you want 15 to be between gte and lt for the open field and 20 to NOT be between gte and lt for close field? Yes. Override close hours for special holidays fall under the closed field so if i want to find something open now I perform this query. It doesnt matter but perhaps i should've used 15 in both conditions. Also, could you show me the mapping/query? ---
**Author:** ![RahulD](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@RahulD](https://discuss.elastic.co/u/RahulD)\ **Post date:** [May 24, 2018, 9:41pm UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/4 "2018-05-24T21:41:17Z")
I would recommend naming the gte as starttime and lt as endtime in both the open and close nested fields. This is mainly to avoid confusion while writing range queries because gte and lt have another meaning inside the nested query. Not that it would cause the query to fail but you will get confused just writing the query if the fields are named gte and lt. Having said that... This is how I would define the mapping: ``` PUT indexname { "mappings" : { "typename" : { "properties" : { "open" : { "type" : "nested", "properties" : { "starttime" : { "type" : "integer" }, "endtime" : { "type" : "integer" } } }, "close" : { "type" : "nested", "properties" : { "starttime" : { "type" : "integer" }, "endtime" : { "type" : "integer" } } } } } } } ``` The query would be: ``` GET /indexname/_search { "size" : 0, "query" : { "bool" : { "must" : [ { "bool" : { "must" : [ { "nested" : { "path" : "open", "query" : { "range" : { "starttime" : { "lte" : 15 } } } } }, { "nested" : { "path" : "open", "query" : { "range" : { "endtime" : { "gt" : 15 } } } } } ] } }, { "bool" : { "must_not" : [ { "bool" : { "must" : [ { "nested" : { "path" : "close", "query" : { "range" : { "starttime" : { "lte" : 15 } } } } }, { "nested" : { "path" : "close", "query" : { "range" : { "endtime" : { "gt" : 15 } } } } } ] } } ] } } ] } } } ``` Let me know if that helps. ---
**Author:** ![panthersquirrel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panthersquirrel/32/31548_2.png) [@panthersquirrel](https://discuss.elastic.co/u/panthersquirrel)\ **Post date:** [May 24, 2018, 10:08pm UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/5 "2018-05-24T22:08:11Z")
Hmm I was hoping for the array elements to be of type integer\_range. Is this even possible with this structure? Or is your way in no way less performant and I should just go with that. I’d be fine if there was no performance difference. ---
**Author:** ![RahulD](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@RahulD](https://discuss.elastic.co/u/RahulD)\ **Post date:** [May 24, 2018, 10:27pm UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/6 "2018-05-24T22:27:10Z")
I don't believe that there would be any difference in performance but would be happy to be proven wrong:) ---
**Author:** ![panthersquirrel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panthersquirrel/32/31548_2.png) [@panthersquirrel](https://discuss.elastic.co/u/panthersquirrel)\ **Post date:** [May 24, 2018, 11:01pm UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/7 "2018-05-24T23:01:29Z")
Ok thank you for your help! ---
**Author:** ![panthersquirrel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panthersquirrel/32/31548_2.png) [@panthersquirrel](https://discuss.elastic.co/u/panthersquirrel)\ **Post date:** [May 26, 2018, 5:40am UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/8 "2018-05-26T05:40:37Z")
It looks like @RahulD's answer was not quite right. It took me a bit to figure out the correct query. ```auto { "query": { "bool": { "must": [ { "nested": { "path": "hours.open", "query": { "bool": { "must": [ { "range": { "hours.open.start": { "lte": 20 } } }, { "range": { "hours.open.end": { "gte": 20 } } } ] } } } } ], "must_not": [ { "nested": { "path": "hours.closed", "query": { "bool": { "must": [ { "range": { "hours.closed.start": { "lte": 20 } } }, { "range": { "hours.closed.end": { "gte": 20 } } } ] } } } } ] } } } ``` See this [SO post](https://stackoverflow.com/questions/50524770/elasticsearch-nested-range-query) i made. Thanks again. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 23, 2018, 5:40am UTC](https://discuss.elastic.co/t/nested-integer-range-mappings-query/133085/9 "2018-06-23T05:40:43Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.