# Range query over dates period **URL:** \ **Category:** Elasticsearch\ **Created:** [May 6, 2013, 4:27pm UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838 "2013-05-06T16:27:00Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![avairet](https://avatars.discourse-cdn.com/v4/letter/a/d78d45/32.png) [@avairet](https://discuss.elastic.co/u/avairet)\ **Post date:** [May 6, 2013, 4:27pm UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838/1 "2013-05-06T16:27:00Z")
Hi, I need to exclude some documents of my resultset considering dates period. My search engine is for apartments rental, so I would like to exclude from resultset, the apartments that are allready booked for the considered period. My example: apartments data in my index: apart\_id: 1 name: Chinois sleepings: 5 rooms: 2 city\_id: 2 district: Opéra facilities: [ Climatisation Internet Parking ] elevator: 0 is\_available\_to\_rent: 1 periods: [ ``` { start_date: 2013-09-04 end_date: 2013-09-06 apartment_id: 1 } { start_date: 2013-09-07 end_date: 2013-09-10 apartment_id: 1 } ``` ] apart\_id: 2 name: Anglais sleepings: 3 rooms: 2 city\_id: 2 district: Opéra facilities: [ Climatisation Internet Parking ] elevator: 0 is\_available\_to\_rent: 1 periods: [ ``` { start_date: 2013-05-13 end_date: 2013-05-17 apartment_id: 1 } { start_date: 2013-09-07 end_date: 2013-09-10 apartment_id: 1 } ``` ] I would like to get only the first apartment in my resultset (e.g. exclude the first period of the second apartment) For example, if I search apartment that is available for the period between 2013-05-10 and 2013-05-15, the second should not match because it has a period that is overlaped by the search period. I try bool query with a range query (or range filter), but it does not work... -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![phill](https://avatars.discourse-cdn.com/v4/letter/p/779978/32.png) [@phill](https://discuss.elastic.co/u/phill)\ **Post date:** [May 6, 2013, 5:07pm UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838/2 "2013-05-06T17:07:39Z")
I think you need to use nested objects, not an array of objects, but you don't show your mapping, so maybe I don't completely understand your issue. To enable nested objects, you will have to use a mapping in your index and not just rely on default mapping. -Paul On 5/6/2013 9:27 AM, avairet wrote: > Hi, > > I need to exclude some documents of my resultset considering dates > period. > [...] > apart\_id: 2 > name: Anglais > sleepings: 3 > rooms: 2 > city\_id: 2 > district: Opéra > facilities: [ > Climatisation > Internet > Parking > ] > elevator: 0 > is\_available\_to\_rent: 1 > periods: [ > > ``` > { > start_date: 2013-05-13 > end_date: 2013-05-17 > apartment_id: 1 > } > { > start_date: 2013-09-07 > end_date: 2013-09-10 > apartment_id: 1 > } > > ``` > > ] -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![sslhj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sslhj/32/119856_2.png) [@sslhj](https://discuss.elastic.co/u/sslhj)\ **Post date:** [May 7, 2013, 1:05am UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838/3 "2013-05-07T01:05:37Z")
hi, you can use bool query with must of term qeurys, such as, { "query": { "filtered": { "query": { "bool": { "must": [ { "term": { "deptPath": "xxx" } }, { "term": { "deptPath": "yyy" } } ] } }, "filter": { "match\_all": {} } } } } Hi, I need to exclude some documents of my resultset considering dates period. My search engine is for apartments rental, so I would like to exclude from resultset, the apartments that are allready booked for the considered period. My example: apartments data in my index: apart\_id: 1 name: Chinois sleepings: 5 rooms: 2 city\_id: 2 district: Opéra facilities: [ Climatisation Internet Parking ] elevator: 0 is\_available\_to\_rent: 1 periods: [ ``` { start_date: 2013-09-04 end_date: 2013-09-06 apartment_id: 1 } { start_date: 2013-09-07 end_date: 2013-09-10 apartment_id: 1 } ``` ] apart\_id: 2 name: Anglais sleepings: 3 rooms: 2 city\_id: 2 district: Opéra facilities: [ Climatisation Internet Parking ] elevator: 0 is\_available\_to\_rent: 1 periods: [ ``` { start_date: 2013-05-13 end_date: 2013-05-17 apartment_id: 1 } { start_date: 2013-09-07 end_date: 2013-09-10 apartment_id: 1 } ``` ] I would like to get only the first apartment in my resultset (e.g. exclude the first period of the second apartment) For example, if I search apartment that is available for the period between 2013-05-10 and 2013-05-15, the second should not match because it has a period that is overlaped by the search period. I try bool query with a range query (or range filter), but it does not work... -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@. For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![avairet](https://avatars.discourse-cdn.com/v4/letter/a/d78d45/32.png) [@avairet](https://discuss.elastic.co/u/avairet)\ **Post date:** [May 7, 2013, 7:59am UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838/4 "2013-05-07T07:59:14Z")
Thanks for answer Paul! Yes I think nested object is the good way... So I have mapped my periods as nested objects, but now, I don't know how to query (or filter) my documents to exclude those who are not available for the considered period... There is a test I have made but without success: { "nested" : { "path" : "periods", "score\_mode" : "total", "query" : { "bool" : { "must\_not" : [ { "range" : {"periods.start\_date" : {"to" : "2013-05-10"}} }, { "range" : {"periods.end\_date" : {"to" : "2013-05-17"}}} ] } } } } Le lundi 6 mai 2013 19:10:48 UTC+2, P Hill a écrit : > I think you need to use nested objects, not an array of objects, but you > don't show your mapping, so maybe I don't completely understand your > issue. > To enable nested objects, you will have to use a mapping in your index > and not just rely on default mapping. > -Paul > > On 5/6/2013 9:27 AM, avairet wrote: > > > Hi, > > > > I need to exclude some documents of my resultset considering dates > > period. > > [...] > > apart\_id: 2 > > name: Anglais > > sleepings: 3 > > rooms: 2 > > city\_id: 2 > > district: Op�ra > > facilities: [ > > Climatisation > > Internet > > Parking > > ] > > elevator: 0 > > is\_available\_to\_rent: 1 > > periods: [ > > > > ``` > > { > > start_date: 2013-05-13 > > end_date: 2013-05-17 > > apartment_id: 1 > > } > > { > > start_date: 2013-09-07 > > end_date: 2013-09-10 > > apartment_id: 1 > > } > > > > ``` > > > > ] -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![phill](https://avatars.discourse-cdn.com/v4/letter/p/779978/32.png) [@phill](https://discuss.elastic.co/u/phill)\ **Post date:** [May 7, 2013, 5:07pm UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838/5 "2013-05-07T17:07:18Z")
You are saying to exclude any entry that has a available start before your start date, this would exclude, for example an entry that started 1900-01-01 🙂 You have a similar problem with the end date. Must\_not means any of expressions (boolean OR) excludes the entry (the rental). So If I listed that my place was available 2000-01-01 to 2100-01-01 I would always be excluded! Hmm, something is wrong. I am assuming the periods are available to rent periods. What you want is a statement that says, if there is an entry has an _available_ period that has a start date on or before the interval of interest (from 2013-05-10 to 2013-05-17 in the example you're using) AND an end date on or after the end date. Does this mean you have it exactly backwards and the query should be "must" instead of "must\_not"? I can't count the number of times I've worked out logic exactly opposite of what I wanted. I would give that a try. -Paul On 5/7/2013 12:59 AM, avairet wrote: > "must\_not" : [ > { > "range" : {"periods.start\_date" : {"to" : "2013-05-10"}} > }, > { > "range" : {"periods.end\_date" : {"to" : "2013-05-17"}}} > ] -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:37am UTC](https://discuss.elastic.co/t/range-query-over-dates-period/11838/6 "2017-07-06T02:37:54Z")