# Search nested collection of ranges

**URL:** <https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685>\
**Category:** Elasticsearch\
**Created:** [November 12, 2012, 9:02pm UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685 "2012-11-12T21:02:12Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![JohnnyPeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnnypeck/32/2624_2.png) [@JohnnyPeck](https://discuss.elastic.co/u/JohnnyPeck)\
**Post date:** [November 12, 2012, 9:02pm UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/1 "2012-11-12T21:02:12Z")

</div>

I really need some guidance with a problem that's been vexing me for days  
now. I spoke with Kimchy on IRC and he suggested I post here. I have an  
index with rental properties. Each property has a nested collection of  
leases with a start and end date. I want the user to query with a start and  
end date that will return properties that DO NOT have a lease that overlaps  
with the submitted start and end. The user can also provide search terms,  
number of bedrooms, etc. Doing that search was not a problem. But filtering  
on the start and end dates is proving problematic.

An example property looks like:

{

```
_index: website
_type: property
_id: 9
_version: 1
_score: 1
_source: {
    id: 9
    ratePerDay: 115.00
    listingTitle: 851 North Glebe Road #2009
    locationNeighborhood.name: Arlington
    leases: [
        {
            start: {
                date: 2009-08-27 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2009-09-26 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2009-11-18 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2010-11-20 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2010-12-16 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2011-02-15 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2011-04-12 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2011-09-29 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2011-10-30 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2011-12-31 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2012-08-22 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2012-11-19 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2012-12-30 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2013-07-15 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
        {
            start: {
                date: 2012-12-30 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
            end: {
                date: 2013-07-15 00:00:00
                timezone_type: 3
                timezone: America/New_York
            }
        }
    ]
}

```

}

A query I've been trying:

{  
"filtered" : {  
"query" : { "match\_all" : {}},  
"filter" : {  
"nested" : {  
"path" : "leases",  
"query" : {  
"bool" : {  
"must\_not" : [  
{  
"range" : {  
"leases.start" : {  
"from" : "2009-05-06 00:00:00",  
"to" : "2009-05-29 00:00:00",  
"include\_lower" : true,  
"include\_upper" : false  
}  
}  
},  
{  
"range" : {  
"leases.end" : {  
"from" : "2009-05-06 00:00:00",  
"to" : "2009-05-29 00:00:00",  
"include\_lower" : true,  
"include\_upper" : false  
}  
}  
}  
]  
}  
}  
}  
}  
}  
}

Any help would be greatly appreciated.

--

---

<div class="post-metadata">

**Author:** ![Chris\_Male](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chris_male/32/2607_2.png) [@Chris\_Male](https://discuss.elastic.co/u/Chris_Male)\
**Post date:** [November 12, 2012, 10:58pm UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/2 "2012-11-12T22:58:28Z")

</div>

What is happening with the above query? You're getting no results, all  
results?

On Tuesday, November 13, 2012 10:02:12 AM UTC+13, jspeck wrote:

> I really need some guidance with a problem that's been vexing me for days  
> now. I spoke with Kimchy on IRC and he suggested I post here. I have an  
> index with rental properties. Each property has a nested collection of  
> leases with a start and end date. I want the user to query with a start and  
> end date that will return properties that DO NOT have a lease that overlaps  
> with the submitted start and end. The user can also provide search terms,  
> number of bedrooms, etc. Doing that search was not a problem. But filtering  
> on the start and end dates is proving problematic.
> 
> An example property looks like:
> 
> {
> 
> ```
> _index: website
> _type: property
> _id: 9
> _version: 1
> _score: 1
> _source: {
> id: 9
> ratePerDay: 115.00
> listingTitle: 851 North Glebe Road #2009
> locationNeighborhood.name: Arlington
> leases: [
> {
> start: {
> date: 2009-08-27 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2009-09-26 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2009-11-18 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2010-11-20 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2010-12-16 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2011-02-15 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2011-04-12 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2011-09-29 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2011-10-30 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2011-12-31 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2012-08-22 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2012-11-19 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2012-12-30 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2013-07-15 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> {
> start: {
> date: 2012-12-30 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> end: {
> date: 2013-07-15 00:00:00
> timezone_type: 3
> timezone: America/New_York
> }
> }
> ]
> }
> 
> ```
> 
> }
> 
> A query I've been trying:
> 
> {  
> "filtered" : {  
> "query" : { "match\_all" : {}},  
> "filter" : {  
> "nested" : {  
> "path" : "leases",  
> "query" : {  
> "bool" : {  
> "must\_not" : [  
> {  
> "range" : {  
> "leases.start" : {  
> "from" : "2009-05-06 00:00:00",  
> "to" : "2009-05-29 00:00:00",  
> "include\_lower" : true,  
> "include\_upper" : false  
> }  
> }  
> },  
> {  
> "range" : {  
> "leases.end" : {  
> "from" : "2009-05-06 00:00:00",  
> "to" : "2009-05-29 00:00:00",  
> "include\_lower" : true,  
> "include\_upper" : false  
> }  
> }  
> }  
> ]  
> }  
> }  
> }  
> }  
> }  
> }
> 
> Any help would be greatly appreciated.

--

---

<div class="post-metadata">

**Author:** ![JohnnyPeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnnypeck/32/2624_2.png) [@JohnnyPeck](https://discuss.elastic.co/u/JohnnyPeck)\
**Post date:** [November 12, 2012, 11:49pm UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/3 "2012-11-12T23:49:07Z")

</div>

It's returning all results. As if the nested query has no effect. What I  
want is if the submitted date range overlaps any lease start or end date  
then the parent must not be returned.

On Monday, November 12, 2012 5:58:28 PM UTC-5, Chris Male wrote:

> What is happening with the above query? You're getting no results, all  
> results?
> 
> On Tuesday, November 13, 2012 10:02:12 AM UTC+13, jspeck wrote:
> 
> > I really need some guidance with a problem that's been vexing me for days  
> > now. I spoke with Kimchy on IRC and he suggested I post here. I have an  
> > index with rental properties. Each property has a nested collection of  
> > leases with a start and end date. I want the user to query with a start and  
> > end date that will return properties that DO NOT have a lease that overlaps  
> > with the submitted start and end. The user can also provide search terms,  
> > number of bedrooms, etc. Doing that search was not a problem. But filtering  
> > on the start and end dates is proving problematic.
> > 
> > An example property looks like:
> > 
> > {
> > 
> > ```
> > _index: website
> > _type: property
> > _id: 9
> > _version: 1
> > _score: 1
> > _source: {
> > id: 9
> > ratePerDay: 115.00
> > listingTitle: 851 North Glebe Road #2009
> > locationNeighborhood.name: Arlington
> > leases: [
> > {
> > start: {
> > date: 2009-08-27 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2009-09-26 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2009-11-18 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2010-11-20 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2010-12-16 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2011-02-15 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2011-04-12 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2011-09-29 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2011-10-30 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2011-12-31 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2012-08-22 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2012-11-19 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2012-12-30 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2013-07-15 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > {
> > start: {
> > date: 2012-12-30 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > end: {
> > date: 2013-07-15 00:00:00
> > timezone_type: 3
> > timezone: America/New_York
> > }
> > }
> > ]
> > }
> > 
> > ```
> > 
> > }
> > 
> > A query I've been trying:
> > 
> > {  
> > "filtered" : {  
> > "query" : { "match\_all" : {}},  
> > "filter" : {  
> > "nested" : {  
> > "path" : "leases",  
> > "query" : {  
> > "bool" : {  
> > "must\_not" : [  
> > {  
> > "range" : {  
> > "leases.start" : {  
> > "from" : "2009-05-06 00:00:00",  
> > "to" : "2009-05-29 00:00:00",  
> > "include\_lower" : true,  
> > "include\_upper" : false  
> > }  
> > }  
> > },  
> > {  
> > "range" : {  
> > "leases.end" : {  
> > "from" : "2009-05-06 00:00:00",  
> > "to" : "2009-05-29 00:00:00",  
> > "include\_lower" : true,  
> > "include\_upper" : false  
> > }  
> > }  
> > }  
> > ]  
> > }  
> > }  
> > }  
> > }  
> > }  
> > }
> > 
> > Any help would be greatly appreciated.

--

---

<div class="post-metadata">

**Author:** ![mvg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mvg/32/98890_2.png) [@mvg](https://discuss.elastic.co/u/mvg)\
**Post date:** [November 13, 2012, 12:46am UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/4 "2012-11-13T00:46:46Z")

</div>

Can you share your mapping? In order to use nested queries, you need  
to use the nested type for field leases.

Martijn

On 13 November 2012 00:49, jspeck [johnny@johnnypeck.com](mailto:johnny@johnnypeck.com) wrote:

> It's returning all results. As if the nested query has no effect. What I  
> want is if the submitted date range overlaps any lease start or end date  
> then the parent must not be returned.
> 
> On Monday, November 12, 2012 5:58:28 PM UTC-5, Chris Male wrote:
> 
> > What is happening with the above query? You're getting no results, all  
> > results?
> > 
> > On Tuesday, November 13, 2012 10:02:12 AM UTC+13, jspeck wrote:
> > 
> > > I really need some guidance with a problem that's been vexing me for days  
> > > now. I spoke with Kimchy on IRC and he suggested I post here. I have an  
> > > index with rental properties. Each property has a nested collection of  
> > > leases with a start and end date. I want the user to query with a start and  
> > > end date that will return properties that DO NOT have a lease that overlaps  
> > > with the submitted start and end. The user can also provide search terms,  
> > > number of bedrooms, etc. Doing that search was not a problem. But filtering  
> > > on the start and end dates is proving problematic.
> > > 
> > > An example property looks like:
> > > 
> > > {
> > > 
> > > ```
> > > _index: website
> > > _type: property
> > > _id: 9
> > > _version: 1
> > > _score: 1
> > > _source: {
> > > id: 9
> > > ratePerDay: 115.00
> > > listingTitle: 851 North Glebe Road #2009
> > > locationNeighborhood.name: Arlington
> > > leases: [
> > > {
> > > start: {
> > > date: 2009-08-27 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2009-09-26 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2009-11-18 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2010-11-20 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2010-12-16 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2011-02-15 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2011-04-12 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2011-09-29 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2011-10-30 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2011-12-31 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2012-08-22 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2012-11-19 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2012-12-30 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2013-07-15 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > {
> > > start: {
> > > date: 2012-12-30 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > end: {
> > > date: 2013-07-15 00:00:00
> > > timezone_type: 3
> > > timezone: America/New_York
> > > }
> > > }
> > > ]
> > > }
> > > 
> > > ```
> > > 
> > > }
> > > 
> > > A query I've been trying:
> > > 
> > > {  
> > > "filtered" : {  
> > > "query" : { "match\_all" : {}},  
> > > "filter" : {  
> > > "nested" : {  
> > > "path" : "leases",  
> > > "query" : {  
> > > "bool" : {  
> > > "must\_not" : [  
> > > {  
> > > "range" : {  
> > > "leases.start" : {  
> > > "from" : "2009-05-06 00:00:00",  
> > > "to" : "2009-05-29 00:00:00",  
> > > "include\_lower" : true,  
> > > "include\_upper" : false  
> > > }  
> > > }  
> > > },  
> > > {  
> > > "range" : {  
> > > "leases.end" : {  
> > > "from" : "2009-05-06 00:00:00",  
> > > "to" : "2009-05-29 00:00:00",  
> > > "include\_lower" : true,  
> > > "include\_upper" : false  
> > > }  
> > > }  
> > > }  
> > > ]  
> > > }  
> > > }  
> > > }  
> > > }  
> > > }  
> > > }
> > > 
> > > Any help would be greatly appreciated.
> 
> --

--  
Met vriendelijke groet,

Martijn van Groningen

--

---

<div class="post-metadata">

**Author:** ![JohnnyPeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnnypeck/32/2624_2.png) [@JohnnyPeck](https://discuss.elastic.co/u/JohnnyPeck)\
**Post date:** [November 13, 2012, 12:56am UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/5 "2012-11-13T00:56:21Z")

</div>

Thank you for taking a look.

My mapping:

{

```
state: open
settings: {
    index.number_of_shards: 5
    index.number_of_replicas: 1
    index.version.created: 191099
}
mappings: {
    property: {
        properties: {
            streetAddress: {
                include_in_all: true
                analyzer: snowball
                type: string
            }
            amenities: {
                include_in_all: true
                type: string
            }
            numFullBathrooms: {
                include_in_all: true
                type: string
            }
            location: {
                lat_lon: true
                type: geo_point
            }
            zipcode: {
                include_in_all: true
                type: string
            }
            cityName: {
                include_in_all: true
                analyzer: snowball
                type: string
            }
            numFloorsInUnit: {
                include_in_all: true
                type: string
            }
            stateCode: {
                include_in_all: true
                type: string
            }
            numBedrooms: {
                include_in_all: true
                type: string
            }
            streetIntersection: {
                include_in_all: true
                type: string
            }
            currentFutureLeases: {
                include_in_all: true
                type: nested
            }
            ratePerDay: {
                include_in_all: true
                type: float
            }
            numHalfBathrooms: {
                include_in_all: true
                type: string
            }
            id: {
                include_in_all: true
                type: string
            }
            leases: {
                include_in_all: true
                properties: {
                    start: {
                        dynamic: true
                        include_in_all: true
                        properties: {
                            timezone: {
                                include_in_all: true
                                type: string
                            }
                            timezone_type: {
                                include_in_all: true
                                type: long
                            }
                            date: {
                                include_in_all: true
                                type: string
                            }
                        }
                    }
                    end: {
                        dynamic: true
                        include_in_all: true
                        properties: {
                            timezone: {
                                include_in_all: true
                                type: string
                            }
                            timezone_type: {
                                include_in_all: true
                                type: long
                            }
                            date: {
                                include_in_all: true
                                type: string
                            }
                        }
                    }
                }
                type: nested
            }
            unitNumber: {
                include_in_all: true
                type: string
            }
            county: {
                include_in_all: true
                type: string
            }
            description: {
                include_in_all: true
                type: string
            }
            name: {
                include_in_all: true
                type: string
            }
            availableDates: {
                include_in_all: true
                type: nested
            }
            locationNeighborhood.name: {
                include_in_all: true
                analyzer: snowball
                type: string
            }
            listingTitle: {
                include_in_all: true
                type: string
            }
            hasDisabledAccess: {
                include_in_all: true
                type: string
            }
        }
    }
}
aliases: []

```

}

On Monday, November 12, 2012 7:47:10 PM UTC-5, Martijn v Groningen wrote:

> Can you share your mapping? In order to use nested queries, you need  
> to use the nested type for field leases.
> 
> Martijn
> 
> On 13 November 2012 00:49, jspeck \<[joh...@johnnypeck.com](mailto:joh...@johnnypeck.com) \<javascript:\>\>  
> wrote:
> 
> > It's returning all results. As if the nested query has no effect. What I  
> > want is if the submitted date range overlaps any lease start or end date  
> > then the parent must not be returned.
> > 
> > On Monday, November 12, 2012 5:58:28 PM UTC-5, Chris Male wrote:
> > 
> > > What is happening with the above query? You're getting no results, all  
> > > results?
> > > 
> > > On Tuesday, November 13, 2012 10:02:12 AM UTC+13, jspeck wrote:
> > > 
> > > > I really need some guidance with a problem that's been vexing me for  
> > > > days  
> > > > now. I spoke with Kimchy on IRC and he suggested I post here. I have  
> > > > an  
> > > > index with rental properties. Each property has a nested collection of  
> > > > leases with a start and end date. I want the user to query with a  
> > > > start and  
> > > > end date that will return properties that DO NOT have a lease that  
> > > > overlaps  
> > > > with the submitted start and end. The user can also provide search  
> > > > terms,  
> > > > number of bedrooms, etc. Doing that search was not a problem. But  
> > > > filtering  
> > > > on the start and end dates is proving problematic.
> > > > 
> > > > An example property looks like:
> > > > 
> > > > {
> > > > 
> > > > ```
> > > > _index: website 
> > > > _type: property 
> > > > _id: 9 
> > > > _version: 1 
> > > > _score: 1 
> > > > _source: { 
> > > > id: 9 
> > > > ratePerDay: 115.00 
> > > > listingTitle: 851 North Glebe Road #2009 
> > > > locationNeighborhood.name: Arlington 
> > > > leases: [ 
> > > > { 
> > > > start: { 
> > > > date: 2009-08-27 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2009-09-26 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2009-11-18 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2010-11-20 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2010-12-16 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2011-02-15 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2011-04-12 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2011-09-29 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2011-10-30 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2011-12-31 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2012-08-22 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2012-11-19 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2012-12-30 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2013-07-15 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > { 
> > > > start: { 
> > > > date: 2012-12-30 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > end: { 
> > > > date: 2013-07-15 00:00:00 
> > > > timezone_type: 3 
> > > > timezone: America/New_York 
> > > > } 
> > > > } 
> > > > ] 
> > > > } 
> > > > 
> > > > ```
> > > > 
> > > > }
> > > > 
> > > > A query I've been trying:
> > > > 
> > > > {  
> > > > "filtered" : {  
> > > > "query" : { "match\_all" : {}},  
> > > > "filter" : {  
> > > > "nested" : {  
> > > > "path" : "leases",  
> > > > "query" : {  
> > > > "bool" : {  
> > > > "must\_not" : [  
> > > > {  
> > > > "range" : {  
> > > > "leases.start" : {  
> > > > "from" : "2009-05-06  
> > > > 00:00:00",  
> > > > "to" : "2009-05-29 00:00:00",  
> > > > "include\_lower" : true,  
> > > > "include\_upper" : false  
> > > > }  
> > > > }  
> > > > },  
> > > > {  
> > > > "range" : {  
> > > > "leases.end" : {  
> > > > "from" : "2009-05-06  
> > > > 00:00:00",  
> > > > "to" : "2009-05-29 00:00:00",  
> > > > "include\_lower" : true,  
> > > > "include\_upper" : false  
> > > > }  
> > > > }  
> > > > }  
> > > > ]  
> > > > }  
> > > > }  
> > > > }  
> > > > }  
> > > > }  
> > > > }
> > > > 
> > > > Any help would be greatly appreciated.
> > 
> > --
> 
> --  
> Met vriendelijke groet,
> 
> Martijn van Groningen

--

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [November 13, 2012, 4:33am UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/6 "2012-11-13T04:33:52Z")

</div>

First of all, the date field is currently indexed as a string and has to be  
fixed for the range queries to work properly. The mapping for date fields  
should be

"date": {  
"include\_in\_all": true,  
"type": "date",  
"format" : "YYYY-MM-dd HH:mm:ss"  
}

The second issues is with the query itself. You are trying to find all  
parents that have no nested documents intersecting with the query range.  
Unfortunately, the nested query doesn't work that way. Nested queries  
return all parents that have any of the matching nested document. So, in  
order to flip "any" query into "none" query, we need to negate the nested  
query itself. In order to do that, we can rephrase your query like this:  
find all parents that don't have any document intersecting with the query:

"filtered" : {  
"query" : { "match\_all" : {}},  
"filter" : {  
"not": {  
"nested" : {  
"path" : "leases",  
"filter" : {  
"and": [  
{  
"range": {  
"leases.end.date": {  
"from": "2009-05-06 00:00:00",  
"include\_upper": true  
}  
}  
}, {  
"range": {  
"leases.start.date": {  
"to": "2009-05-29 00:00:00",  
"include\_lower": true  
}  
}  
}  
]  
}  
}  
}  
}  
}

On Monday, November 12, 2012 7:56:21 PM UTC-5, jspeck wrote:

> Thank you for taking a look.
> 
> My mapping:
> 
> {
> 
> ```
> state: open
> settings: {
> index.number_of_shards: 5
> index.number_of_replicas: 1
> index.version.created: 191099
> }
> mappings: {
> property: {
> properties: {
> streetAddress: {
> include_in_all: true
> analyzer: snowball
> type: string
> }
> amenities: {
> include_in_all: true
> type: string
> }
> numFullBathrooms: {
> include_in_all: true
> type: string
> }
> location: {
> lat_lon: true
> type: geo_point
> }
> zipcode: {
> include_in_all: true
> type: string
> }
> cityName: {
> include_in_all: true
> analyzer: snowball
> type: string
> }
> numFloorsInUnit: {
> include_in_all: true
> type: string
> }
> stateCode: {
> include_in_all: true
> type: string
> }
> numBedrooms: {
> include_in_all: true
> type: string
> }
> streetIntersection: {
> include_in_all: true
> type: string
> }
> currentFutureLeases: {
> include_in_all: true
> type: nested
> }
> ratePerDay: {
> include_in_all: true
> type: float
> }
> numHalfBathrooms: {
> include_in_all: true
> type: string
> }
> id: {
> include_in_all: true
> type: string
> }
> leases: {
> include_in_all: true
> properties: {
> start: {
> dynamic: true
> include_in_all: true
> properties: {
> timezone: {
> include_in_all: true
> type: string
> }
> timezone_type: {
> include_in_all: true
> type: long
> }
> date: {
> include_in_all: true
> type: string
> }
> }
> }
> end: {
> dynamic: true
> include_in_all: true
> properties: {
> timezone: {
> include_in_all: true
> type: string
> }
> timezone_type: {
> include_in_all: true
> type: long
> }
> date: {
> include_in_all: true
> type: string
> }
> }
> }
> }
> type: nested
> }
> unitNumber: {
> include_in_all: true
> type: string
> }
> county: {
> include_in_all: true
> type: string
> }
> description: {
> include_in_all: true
> type: string
> }
> name: {
> include_in_all: true
> type: string
> }
> availableDates: {
> include_in_all: true
> type: nested
> }
> locationNeighborhood.name: {
> include_in_all: true
> analyzer: snowball
> type: string
> }
> listingTitle: {
> include_in_all: true
> type: string
> }
> hasDisabledAccess: {
> include_in_all: true
> type: string
> }
> }
> }
> }
> aliases: []
> 
> ```
> 
> }
> 
> On Monday, November 12, 2012 7:47:10 PM UTC-5, Martijn v Groningen wrote:
> 
> > Can you share your mapping? In order to use nested queries, you need  
> > to use the nested type for field leases.
> > 
> > Martijn
> > 
> > On 13 November 2012 00:49, jspeck [joh...@johnnypeck.com](mailto:joh...@johnnypeck.com) wrote:
> > 
> > > It's returning all results. As if the nested query has no effect. What  
> > > I  
> > > want is if the submitted date range overlaps any lease start or end  
> > > date  
> > > then the parent must not be returned.
> > > 
> > > On Monday, November 12, 2012 5:58:28 PM UTC-5, Chris Male wrote:
> > > 
> > > > What is happening with the above query? You're getting no results,  
> > > > all  
> > > > results?
> > > > 
> > > > On Tuesday, November 13, 2012 10:02:12 AM UTC+13, jspeck wrote:
> > > > 
> > > > > I really need some guidance with a problem that's been vexing me for  
> > > > > days  
> > > > > now. I spoke with Kimchy on IRC and he suggested I post here. I have  
> > > > > an  
> > > > > index with rental properties. Each property has a nested collection  
> > > > > of  
> > > > > leases with a start and end date. I want the user to query with a  
> > > > > start and  
> > > > > end date that will return properties that DO NOT have a lease that  
> > > > > overlaps  
> > > > > with the submitted start and end. The user can also provide search  
> > > > > terms,  
> > > > > number of bedrooms, etc. Doing that search was not a problem. But  
> > > > > filtering  
> > > > > on the start and end dates is proving problematic.
> > > > > 
> > > > > An example property looks like:
> > > > > 
> > > > > {
> > > > > 
> > > > > ```
> > > > > _index: website 
> > > > > _type: property 
> > > > > _id: 9 
> > > > > _version: 1 
> > > > > _score: 1 
> > > > > _source: { 
> > > > > id: 9 
> > > > > ratePerDay: 115.00 
> > > > > listingTitle: 851 North Glebe Road #2009 
> > > > > locationNeighborhood.name: Arlington 
> > > > > leases: [ 
> > > > > { 
> > > > > start: { 
> > > > > date: 2009-08-27 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2009-09-26 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2009-11-18 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2010-11-20 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2010-12-16 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2011-02-15 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2011-04-12 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2011-09-29 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2011-10-30 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2011-12-31 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2012-08-22 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2012-11-19 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2012-12-30 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2013-07-15 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > { 
> > > > > start: { 
> > > > > date: 2012-12-30 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > end: { 
> > > > > date: 2013-07-15 00:00:00 
> > > > > timezone_type: 3 
> > > > > timezone: America/New_York 
> > > > > } 
> > > > > } 
> > > > > ] 
> > > > > } 
> > > > > 
> > > > > ```
> > > > > 
> > > > > }
> > > > > 
> > > > > A query I've been trying:
> > > > > 
> > > > > {  
> > > > > "filtered" : {  
> > > > > "query" : { "match\_all" : {}},  
> > > > > "filter" : {  
> > > > > "nested" : {  
> > > > > "path" : "leases",  
> > > > > "query" : {  
> > > > > "bool" : {  
> > > > > "must\_not" : [  
> > > > > {  
> > > > > "range" : {  
> > > > > "leases.start" : {  
> > > > > "from" : "2009-05-06  
> > > > > 00:00:00",  
> > > > > "to" : "2009-05-29 00:00:00",  
> > > > > "include\_lower" : true,  
> > > > > "include\_upper" : false  
> > > > > }  
> > > > > }  
> > > > > },  
> > > > > {  
> > > > > "range" : {  
> > > > > "leases.end" : {  
> > > > > "from" : "2009-05-06  
> > > > > 00:00:00",  
> > > > > "to" : "2009-05-29 00:00:00",  
> > > > > "include\_lower" : true,  
> > > > > "include\_upper" : false  
> > > > > }  
> > > > > }  
> > > > > }  
> > > > > ]  
> > > > > }  
> > > > > }  
> > > > > }  
> > > > > }  
> > > > > }  
> > > > > }
> > > > > 
> > > > > Any help would be greatly appreciated.
> > > 
> > > --
> > 
> > --  
> > Met vriendelijke groet,
> > 
> > Martijn van Groningen

--

---

<div class="post-metadata">

**Author:** ![phill](https://avatars.discourse-cdn.com/v4/letter/p/779978/32.png) [@phill](https://discuss.elastic.co/u/phill)\
**Post date:** [November 15, 2012, 1:24am UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/7 "2012-11-15T01:24:53Z")

</div>

On 11/12/2012 8:33 PM, Igor Motov wrote:

> The second issues is with the query itself. You are trying to find all  
> parents that have no nested documents intersecting with the query  
> range. Unfortunately, the nested query doesn't work that way.

But the query says "must\_not" with two criteria? Why is that not what  
he asked for: parents that don't have children that don't have the  
specified ranges?  
I after staring at this for awhile I final saw the problem. Sure some  
nested objects don't have the range, but another nested of the same  
parent passes the test allowing the parent to pass which is apparently  
either all or close to all in Johnny's case.

-Paul

--

---

<div class="post-metadata">

**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, 3:04am UTC](https://discuss.elastic.co/t/search-nested-collection-of-ranges/9685/8 "2017-07-06T03:04:11Z")

</div>