# Date Range queries not working with must\_not in nested context **URL:** \ **Category:** Elasticsearch\ **Created:** [October 26, 2018, 3:21pm UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231 "2018-10-26T15:21:04Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![titani0us](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/titani0us/32/31492_2.png) [@titani0us](https://discuss.elastic.co/u/titani0us)\ **Post date:** [October 26, 2018, 3:21pm UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231/1 "2018-10-26T15:21:04Z")
Hello, Running a must\_not bool query on a nested object with dates returns values within the range even though the must\_not operator was used. Here is the example query: ``` { "query" : { "bool" : { "must" : { "match" : { "is_active" : true } }, "must_not" : { "nested" : { "path" : "nonAvailability", "query" : { "range" : { "nonAvailability.date" : { "gte" : "2018-11-05", "lt" : "2018-11-08", "format" : "YYYY-MM-DD" } } } } } } } } ``` Here is an example document being returned even though there are dates that fall within the range clause: { "is\_active" : true, "nonAvailability: { "date": "2018-11-03" }, { "date": "2018-11-04" }, { "date": "2018-11-05" }, { "date": "2018-11-06" }, { "date": "2018-10-24" } } The mapping for the field is specified like so: ``` "nonAvailability": { "type": "nested", "properties": { "date": { "type": "date" } } } ``` Has anyone seen this type of behavior before? Any thoughts on how to fix this? ---
**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\ **Post date:** [October 29, 2018, 4:33pm UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231/2 "2018-10-29T16:33:34Z")
The [date formatter](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html) in the query is incorrect. Instead of `YYYY-MM-DD` you should use `yyyy-MM-dd`. Or you could omit the `format` altogether, as the date format you are using will already be recognized as a valid date. ---
**Author:** ![titani0us](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/titani0us/32/31492_2.png) [@titani0us](https://discuss.elastic.co/u/titani0us)\ **Post date:** [November 2, 2018, 2:34am UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231/3 "2018-11-02T02:34:34Z")
Hi @abdon, Thanks for your response! This is a good catch - I removed it all together but the query still does not work as expected on ES6.4. @Peter_Steenbergen actually suggested I do this via a parent-child relationship, but my understanding of nested documents was that they essentially achieved the same thing in a single document, so I'm confused as to why a child type would solve this query. Are you able to reproduce the same result? ---
**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\ **Post date:** [November 2, 2018, 10:14am UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231/4 "2018-11-02T10:14:50Z")
I cannot reproduce your issue. If I remove the `format` from the query, it works as it should, unless I am misunderstanding the problem. The following only returns the document that does not contain any date within the specified range: ```auto DELETE test PUT test { "mappings": { "_doc": { "properties": { "nonAvailability": { "type": "nested", "properties": { "date": { "type": "date" } } } } } } } PUT test/_doc/1 { "is_active": true, "nonAvailability": [ { "date": "2018-11-03" }, { "date": "2018-11-04" }, { "date": "2018-11-05" }, { "date": "2018-11-06" }, { "date": "2018-10-24" } ] } PUT test/_doc/2 { "is_active": true, "nonAvailability": [ { "date": "2019-11-03" }, { "date": "2019-11-04" }, { "date": "2019-11-05" }, { "date": "2019-11-06" }, { "date": "2019-10-24" } ] } GET test/_search { "query": { "bool": { "must": { "match": { "is_active": true } }, "must_not": { "nested": { "path": "nonAvailability", "query": { "range": { "nonAvailability.date": { "gte": "2018-11-05", "lt": "2018-11-08" } } } } } } } } ``` ---
**Author:** ![titani0us](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/titani0us/32/31492_2.png) [@titani0us](https://discuss.elastic.co/u/titani0us)\ **Post date:** [November 5, 2018, 1:58pm UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231/5 "2018-11-05T13:58:38Z")
@abdon - Thanks for your response on this. Not sure why, but this morning reviewing this again, the query appears to be performing correctly. The availability object is updated frequently. Is it possible old data was stilled cached and was being served by the query? I'm not super familiar with the inner workings of ElasticSearch. Thanks again for all of your help. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 3, 2018, 1:58pm UTC](https://discuss.elastic.co/t/date-range-queries-not-working-with-must-not-in-nested-context/154231/6 "2018-12-03T13:58:43Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.