Search nested collection of ranges

Elastic Stack Elasticsearch
1

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.

--

2

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.

--

3

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.

--

4

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 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

--

5

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 <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

--

6

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 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

--

7

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

--