Nested boolean range query returning values outside of its range


(doug livesey) #1

Hi, I have a bunch of records in an index called item, with varying prices.
I'm trying to construct a query that will allow me to retrieve items under
a particular category_id, and in one or more price ranges.
The query I've come up with is this:

{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"category_id": 1
}
},
{
"bool": {
"should": [
{
"range": {
"price": {
"to": 4.99,
"from": 0.0
}
}
}
]
}
}
]
}
}
}

I was hoping that it would return only items in the category_id 1, with a
price between 0.0 and 4.99 -- however, when I run it, I get only items in
category_id 1 well enough, but I also get all items under that
category_id, including those with a price of over 4.99.
Could someone advise me as to where I am going wrong?
Thankyou for any & all assistance.
Doug.


(doug livesey) #2

Hi -- I've been trying to get this query to work, and tried a filtered
query (as another post suggested), but I still have exactly the same
problem.
The current attempt looks like this:
{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"filtered": {
"filter": {
"and": [
{
"or": [
{
"range": {
"price": {
"to": 4.99,
"from": 0
}
}
}
]
}
]
},
"query": {
"term": {
"category_id": 1
}
}
}
}
}

Basically, I want to select all items under a given category_id, and filter
them on (potentially more than one) price range, as well as a number of
other terms filters (for things like colour_id, etc.).
Am I at least heading in sort-of the right direction with this approach?
Could anyone offer me any insight into what I'm missing about how this
works that makes me surprised that my efforts aren't returning what I
expect them to?
Thanks for any & all help,
Doug.

On 14 November 2011 19:49, doug livesey biot023@gmail.com wrote:

Hi, I have a bunch of records in an index called item, with varying prices.
I'm trying to construct a query that will allow me to retrieve items under
a particular category_id, and in one or more price ranges.
The query I've come up with is this:

{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"category_id": 1
}
},
{
"bool": {
"should": [
{
"range": {
"price": {
"to": 4.99,
"from": 0.0
}
}
}
]
}
}
]
}
}
}

I was hoping that it would return only items in the category_id 1, with a
price between 0.0 and 4.99 -- however, when I run it, I get only items in
category_id 1 well enough, but I also get all items under that
category_id, including those with a price of over 4.99.
Could someone advise me as to where I am going wrong?
Thankyou for any & all assistance.
Doug.


(Shay Banon) #3

Both search requests look good. Can you gist a simple recreation that shows
this? A few curl requests that index fresh data into a fresh index, and the
search request that shows wrong results returning.

On Tue, Nov 15, 2011 at 1:12 PM, doug livesey biot023@gmail.com wrote:

Hi -- I've been trying to get this query to work, and tried a filtered
query (as another post suggested), but I still have exactly the same
problem.
The current attempt looks like this:
{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"filtered": {
"filter": {
"and": [
{
"or": [
{
"range": {
"price": {
"to": 4.99,
"from": 0
}
}
}
]
}
]
},
"query": {
"term": {
"category_id": 1
}
}
}
}
}

Basically, I want to select all items under a given category_id, and
filter them on (potentially more than one) price range, as well as a number
of other terms filters (for things like colour_id, etc.).
Am I at least heading in sort-of the right direction with this approach?
Could anyone offer me any insight into what I'm missing about how this
works that makes me surprised that my efforts aren't returning what I
expect them to?
Thanks for any & all help,
Doug.

On 14 November 2011 19:49, doug livesey biot023@gmail.com wrote:

Hi, I have a bunch of records in an index called item, with varying
prices.
I'm trying to construct a query that will allow me to retrieve items
under a particular category_id, and in one or more price ranges.
The query I've come up with is this:

{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"category_id": 1
}
},
{
"bool": {
"should": [
{
"range": {
"price": {
"to": 4.99,
"from": 0.0
}
}
}
]
}
}
]
}
}
}

I was hoping that it would return only items in the category_id 1, with a
price between 0.0 and 4.99 -- however, when I run it, I get only items in
category_id 1 well enough, but I also get all items under that
category_id, including those with a price of over 4.99.
Could someone advise me as to where I am going wrong?
Thankyou for any & all assistance.
Doug.


(doug livesey) #4

Hi, sure -- the indexing happens automatically to a fresh index as part of
testing -- the test process automatically clears the index before tests
commence.
The output of the search is here: https://gist.github.com/1367263
This is for the second search, although it looks the same for the first.
& thanks for looking at this.

On 15 November 2011 11:27, Shay Banon kimchy@gmail.com wrote:

Both search requests look good. Can you gist a simple recreation that
shows this? A few curl requests that index fresh data into a fresh index,
and the search request that shows wrong results returning.

On Tue, Nov 15, 2011 at 1:12 PM, doug livesey biot023@gmail.com wrote:

Hi -- I've been trying to get this query to work, and tried a filtered
query (as another post suggested), but I still have exactly the same
problem.
The current attempt looks like this:
{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"filtered": {
"filter": {
"and": [
{
"or": [
{
"range": {
"price": {
"to": 4.99,
"from": 0
}
}
}
]
}
]
},
"query": {
"term": {
"category_id": 1
}
}
}
}
}

Basically, I want to select all items under a given category_id, and
filter them on (potentially more than one) price range, as well as a number
of other terms filters (for things like colour_id, etc.).
Am I at least heading in sort-of the right direction with this approach?
Could anyone offer me any insight into what I'm missing about how this
works that makes me surprised that my efforts aren't returning what I
expect them to?
Thanks for any & all help,
Doug.

On 14 November 2011 19:49, doug livesey biot023@gmail.com wrote:

Hi, I have a bunch of records in an index called item, with varying
prices.
I'm trying to construct a query that will allow me to retrieve items
under a particular category_id, and in one or more price ranges.
The query I've come up with is this:

{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"category_id": 1
}
},
{
"bool": {
"should": [
{
"range": {
"price": {
"to": 4.99,
"from": 0.0
}
}
}
]
}
}
]
}
}
}

I was hoping that it would return only items in the category_id 1, with
a price between 0.0 and 4.99 -- however, when I run it, I get only items in
category_id 1 well enough, but I also get all items under that
category_id, including those with a price of over 4.99.
Could someone advise me as to where I am going wrong?
Thankyou for any & all assistance.
Doug.


(doug livesey) #5

Sorry -- just realised why you need the put requests, too -- having a long
day, sorry!
Here they are:

curl -XPUT "http://localhost:9200/folksy_test_search/items/1" -d
'{"price":10.0,"material_ids":[1],"listed_at":"20110915T152837","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000001","id":1,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

curl -XPUT "http://localhost:9200/folksy_test_search/items/2" -d
'{"price":10.0,"material_ids":[1],"listed_at":"20110915T152837","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000003","id":2,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

curl -XPUT "http://localhost:9200/folksy_test_search/items/3" -d
'{"price":3.0,"material_ids":[1],"listed_at":"20110915T152838","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000005","id":3,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

curl -XPUT "http://localhost:9200/folksy_test_search/items/4" -d
'{"price":3.0,"material_ids":[1],"listed_at":"20110915T152839","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000007","id":4,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

Obviously, I only want the latter two items to appear in the query.
Thanks again,
Doug.

On 15 November 2011 14:59, doug livesey biot023@gmail.com wrote:

Hi, sure -- the indexing happens automatically to a fresh index as part of
testing -- the test process automatically clears the index before tests
commence.
The output of the search is here: https://gist.github.com/1367263
This is for the second search, although it looks the same for the first.
& thanks for looking at this.

On 15 November 2011 11:27, Shay Banon kimchy@gmail.com wrote:

Both search requests look good. Can you gist a simple recreation that
shows this? A few curl requests that index fresh data into a fresh index,
and the search request that shows wrong results returning.

On Tue, Nov 15, 2011 at 1:12 PM, doug livesey biot023@gmail.com wrote:

Hi -- I've been trying to get this query to work, and tried a filtered
query (as another post suggested), but I still have exactly the same
problem.
The current attempt looks like this:
{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"filtered": {
"filter": {
"and": [
{
"or": [
{
"range": {
"price": {
"to": 4.99,
"from": 0
}
}
}
]
}
]
},
"query": {
"term": {
"category_id": 1
}
}
}
}
}

Basically, I want to select all items under a given category_id, and
filter them on (potentially more than one) price range, as well as a number
of other terms filters (for things like colour_id, etc.).
Am I at least heading in sort-of the right direction with this approach?
Could anyone offer me any insight into what I'm missing about how this
works that makes me surprised that my efforts aren't returning what I
expect them to?
Thanks for any & all help,
Doug.

On 14 November 2011 19:49, doug livesey biot023@gmail.com wrote:

Hi, I have a bunch of records in an index called item, with varying
prices.
I'm trying to construct a query that will allow me to retrieve items
under a particular category_id, and in one or more price ranges.
The query I've come up with is this:

{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"category_id": 1
}
},
{
"bool": {
"should": [
{
"range": {
"price": {
"to": 4.99,
"from": 0.0
}
}
}
]
}
}
]
}
}
}

I was hoping that it would return only items in the category_id 1, with
a price between 0.0 and 4.99 -- however, when I run it, I get only items in
category_id 1 well enough, but I also get all items under that
category_id, including those with a price of over 4.99.
Could someone advise me as to where I am going wrong?
Thankyou for any & all assistance.
Doug.


(doug livesey) #6

Damn' -- even this fails:
$ curl -XGET "
http://localhost:9200/folksy_test_search/items/_search?pretty=true" -d
'{"size":18,"sort":[{"listed_at":"desc"}],"from":0,"query":{"filtered":{"filter":{"range":{"price":{"to":4.99,"from":0.0}}},"query":{"term":{"category_id":1}}}}}'

I've removed the "and" filter and just put the "range" filter in directly,
and still it returns items with a price outside the range.

On 15 November 2011 15:40, doug livesey biot023@gmail.com wrote:

Sorry -- just realised why you need the put requests, too -- having a long
day, sorry!
Here they are:

curl -XPUT "http://localhost:9200/folksy_test_search/items/1" -d
'{"price":10.0,"material_ids":[1],"listed_at":"20110915T152837","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000001","id":1,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

curl -XPUT "http://localhost:9200/folksy_test_search/items/2" -d
'{"price":10.0,"material_ids":[1],"listed_at":"20110915T152837","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000003","id":2,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

curl -XPUT "http://localhost:9200/folksy_test_search/items/3" -d
'{"price":3.0,"material_ids":[1],"listed_at":"20110915T152838","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000005","id":3,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

curl -XPUT "http://localhost:9200/folksy_test_search/items/4" -d
'{"price":3.0,"material_ids":[1],"listed_at":"20110915T152839","place_id":null,"title":"Red
Jumper","section_id":1,"user_login":"user000007","id":4,"category_id":1,"colour_ids":[1],"description":"A
beautiful red jumper hand-knitted with quality wool.","keywords":"Men
Clothes Jumpers Blue Cotton","subcategory_id":1}'

Obviously, I only want the latter two items to appear in the query.
Thanks again,
Doug.

On 15 November 2011 14:59, doug livesey biot023@gmail.com wrote:

Hi, sure -- the indexing happens automatically to a fresh index as part
of testing -- the test process automatically clears the index before tests
commence.
The output of the search is here: https://gist.github.com/1367263
This is for the second search, although it looks the same for the first.
& thanks for looking at this.

On 15 November 2011 11:27, Shay Banon kimchy@gmail.com wrote:

Both search requests look good. Can you gist a simple recreation that
shows this? A few curl requests that index fresh data into a fresh index,
and the search request that shows wrong results returning.

On Tue, Nov 15, 2011 at 1:12 PM, doug livesey biot023@gmail.com wrote:

Hi -- I've been trying to get this query to work, and tried a filtered
query (as another post suggested), but I still have exactly the same
problem.
The current attempt looks like this:
{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"filtered": {
"filter": {
"and": [
{
"or": [
{
"range": {
"price": {
"to": 4.99,
"from": 0
}
}
}
]
}
]
},
"query": {
"term": {
"category_id": 1
}
}
}
}
}

Basically, I want to select all items under a given category_id, and
filter them on (potentially more than one) price range, as well as a number
of other terms filters (for things like colour_id, etc.).
Am I at least heading in sort-of the right direction with this approach?
Could anyone offer me any insight into what I'm missing about how this
works that makes me surprised that my efforts aren't returning what I
expect them to?
Thanks for any & all help,
Doug.

On 14 November 2011 19:49, doug livesey biot023@gmail.com wrote:

Hi, I have a bunch of records in an index called item, with varying
prices.
I'm trying to construct a query that will allow me to retrieve items
under a particular category_id, and in one or more price ranges.
The query I've come up with is this:

{
"size": 18,
"sort": [
{
"listed_at": "desc"
}
],
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"category_id": 1
}
},
{
"bool": {
"should": [
{
"range": {
"price": {
"to": 4.99,
"from": 0.0
}
}
}
]
}
}
]
}
}
}

I was hoping that it would return only items in the category_id 1,
with a price between 0.0 and 4.99 -- however, when I run it, I get only
items in category_id 1 well enough, but I also get all items under that
category_id, including those with a price of over 4.99.
Could someone advise me as to where I am going wrong?
Thankyou for any & all assistance.
Doug.


(doug livesey) #7

So the full gist of actions is here: https://gist.github.com/1367538
The last two lines are search queries, that I believe should only return
the items with a price of 3.00.
Sorry I keep posting as I realise I've not given a full description --
we're getting close to our big deploy, and my head's fragmenting! :slight_smile:
Cheers,
Doug.


(Clinton Gormley) #8

Hi Doug

On Tue, 2011-11-15 at 16:26 +0000, doug livesey wrote:

Damn' -- even this fails:
$ curl -XGET
"http://localhost:9200/folksy_test_search/items/_search?pretty=true"
-d
'{"size":18,"sort":[{"listed_at":"desc"}],"from":0,"query":{"filtered":{"filter":{"range":{"price":{"to":4.99,"from":0.0}}},"query":{"term":{"category_id":1}}}}}'

It is really much easier to help if these code examples are gisted and
formatted properly. There are just too many {{{{{}}}} to check
visually.

Also, if you create a complete example, you will probably find your
error.

I think that, if you check the mapping for 'item' you will find that the
'price' field is mapped as a string, so your "from" and "to" parameters
are being treated as strings, not as numbers.

When I try your example docs plus your example search, then it works for
me. If I change the mapping for "price" to "string", then I get all
results back.

You probably indexed a price as "0.50" early on, which messed up your
mapping.

good luck :slight_smile:

clint

I've removed the "and" filter and just put the "range" filter in
directly, and still it returns items with a price outside the range.

On 15 November 2011 15:40, doug livesey biot023@gmail.com wrote:
Sorry -- just realised why you need the put requests, too --
having a long day, sorry!
Here they are:

    curl -XPUT "http://localhost:9200/folksy_test_search/items/1"
    -d
    '{"price":10.0,"material_ids":[1],"listed_at":"20110915T152837","place_id":null,"title":"Red Jumper","section_id":1,"user_login":"user000001","id":1,"category_id":1,"colour_ids":[1],"description":"A beautiful red jumper hand-knitted with quality wool.","keywords":"Men Clothes Jumpers Blue Cotton","subcategory_id":1}' 
    
    
    curl -XPUT "http://localhost:9200/folksy_test_search/items/2"
    -d
    '{"price":10.0,"material_ids":[1],"listed_at":"20110915T152837","place_id":null,"title":"Red Jumper","section_id":1,"user_login":"user000003","id":2,"category_id":1,"colour_ids":[1],"description":"A beautiful red jumper hand-knitted with quality wool.","keywords":"Men Clothes Jumpers Blue Cotton","subcategory_id":1}' 
    
    
    curl -XPUT "http://localhost:9200/folksy_test_search/items/3"
    -d
    '{"price":3.0,"material_ids":[1],"listed_at":"20110915T152838","place_id":null,"title":"Red Jumper","section_id":1,"user_login":"user000005","id":3,"category_id":1,"colour_ids":[1],"description":"A beautiful red jumper hand-knitted with quality wool.","keywords":"Men Clothes Jumpers Blue Cotton","subcategory_id":1}' 
    
    
    curl -XPUT "http://localhost:9200/folksy_test_search/items/4"
    -d
    '{"price":3.0,"material_ids":[1],"listed_at":"20110915T152839","place_id":null,"title":"Red Jumper","section_id":1,"user_login":"user000007","id":4,"category_id":1,"colour_ids":[1],"description":"A beautiful red jumper hand-knitted with quality wool.","keywords":"Men Clothes Jumpers Blue Cotton","subcategory_id":1}' 
    
    
    Obviously, I only want the latter two items to appear in the
    query. 
    Thanks again, 
       Doug. 
    
    On 15 November 2011 14:59, doug livesey <biot023@gmail.com>
    wrote:
            Hi, sure -- the indexing happens automatically to a
            fresh index as part of testing -- the test process
            automatically clears the index before tests commence. 
            The output of the search is
            here: https://gist.github.com/1367263 
            This is for the second search, although it looks the
            same for the first. 
            & thanks for looking at this. 
            
            
            On 15 November 2011 11:27, Shay Banon
            <kimchy@gmail.com> wrote: 
                    Both search requests look good. Can you gist a
                    simple recreation that shows this? A few curl
                    requests that index fresh data into a fresh
                    index, and the search request that shows wrong
                    results returning. 
                    
                    
                    On Tue, Nov 15, 2011 at 1:12 PM, doug livesey
                    <biot023@gmail.com> wrote:
                            Hi -- I've been trying to get this
                            query to work, and tried a filtered
                            query (as another post suggested), but
                            I still have exactly the same
                            problem. 
                            The current attempt looks like this: 
                            { 
                                "size": 18, 
                                "sort": [ 
                                    { 
                                        "listed_at": "desc" 
                                    } 
                                ], 
                                "from": 0, 
                                "query": { 
                                    "filtered": { 
                                        "filter": { 
                                            "and": [ 
                                                { 
                                                    "or": [ 
                                                        { 
                            
                            "range": { 
                            
                            "price": { 
                            
                            "to": 4.99, 
                            
                            "from": 0 
                                                                } 
                                                            } 
                                                        } 
                                                    ] 
                                                } 
                                            ] 
                                        }, 
                                        "query": { 
                                            "term": { 
                                                "category_id": 1 
                                            } 
                                        } 
                                    } 
                                } 
                            } 
                            
                            
                            Basically, I want to select all items
                            under a given category_id, and filter
                            them on (potentially more than one)
                            price range, as well as a number of
                            other terms filters (for things like
                            colour_id, etc.). 
                            Am I at least heading in sort-of the
                            right direction with this approach? 
                            Could anyone offer me any insight into
                            what I'm missing about how this works
                            that makes me surprised that my
                            efforts aren't returning what I expect
                            them to? 
                            Thanks for any & all help, 
                               Doug. 
                            
                            On 14 November 2011 19:49, doug
                            livesey <biot023@gmail.com> wrote:
                                    Hi, I have a bunch of records
                                    in an index called item, with
                                    varying prices. 
                                    I'm trying to construct a
                                    query that will allow me to
                                    retrieve items under a
                                    particular category_id, and in
                                    one or more price ranges. 
                                    The query I've come up with is
                                    this: 
                                    
                                    
                                    { 
                                        "size": 18, 
                                        "sort": [ 
                                            { 
                                                "listed_at":
                                    "desc" 
                                            } 
                                        ], 
                                        "from": 0, 
                                        "query": { 
                                            "bool": { 
                                                "must": [ 
                                                    { 
                                                        "term": { 
                                    
                                    "category_id": 1 
                                                        } 
                                                    }, 
                                                    { 
                                                        "bool": { 
                                    
                                    "should": [ 
                                                                { 
                                    
                                    "range": { 
                                    
                                    "price": { 
                                    
                                    "to": 4.99, 
                                    
                                    "from": 0.0 
                                    
                                          } 
                                    
                                      } 
                                                                } 
                                                            ] 
                                                        } 
                                                    } 
                                                ] 
                                            } 
                                        } 
                                    } 
                                    
                                    
                                    I was hoping that it would
                                    return only items in the
                                    category_id 1, with a price
                                    between 0.0 and 4.99 --
                                    however, when I run it, I get
                                    only items in category_id 1
                                    well enough, but I also get
                                    *all* items under that
                                    category_id, including those
                                    with a price of over 4.99. 
                                    Could someone advise me as to
                                    where I am going wrong? 
                                    Thankyou for any & all
                                    assistance. 
                                       Doug. 

(Clinton Gormley) #9

On Tue, 2011-11-15 at 16:40 +0000, doug livesey wrote:

So the full gist of actions is here: https://gist.github.com/1367538
The last two lines are search queries, that I believe should only
return the items with a price of 3.00.

I've tried out your examples, and bot queries return 2 results.

Have you deleted your index and run your examples as you have laid them
out?

If so, and you're still getting wrong results, what version of ES are
you using?

clint


(doug livesey) #10

Deleting the index did it -- thankyou very much!
& sorry about the fragmentary posting -- I think I may have overdone
the caffeine, today!
One final question regarding the two queries I put together to try to solve
this -- which of them would be the more efficient?
The first, that used a boolean query?
Or the second, that used a filtered query?
(Please say the second -- that's where the code's at, now! :wink: )
& thankyou very much, once again,
Doug.

On 15 November 2011 16:51, Clinton Gormley clint@traveljury.com wrote:

On Tue, 2011-11-15 at 16:40 +0000, doug livesey wrote:

So the full gist of actions is here: https://gist.github.com/1367538
The last two lines are search queries, that I believe should only
return the items with a price of 3.00.

I've tried out your examples, and bot queries return 2 results.

Have you deleted your index and run your examples as you have laid them
out?

If so, and you're still getting wrong results, what version of ES are
you using?

clint


(Clinton Gormley) #11

On Tue, 2011-11-15 at 17:14 +0000, doug livesey wrote:

Deleting the index did it -- thankyou very much!

glad it helped

& sorry about the fragmentary posting -- I think I may have overdone
the caffeine, today!

I could hear the desperation :slight_smile:

One final question regarding the two queries I put together to try to
solve this -- which of them would be the more efficient?
The first, that used a boolean query?
Or the second, that used a filtered query?
(Please say the second -- that's where the code's at, now! :wink: )

The filtered query. Also, your category_id is also just a filter - no
need to run it as a query.

You could do this instead:

curl -XGET
'http://127.0.0.1:9200/folksy_test_search/items/_search?pretty=1' -d '
{
"sort" : [
{
"listed_at" : "desc"
}
],
"query" : {
"constant_score" : {
"filter" : {
"and" : [
{
"term" : {
"category_id" : 1
}
},
{
"numeric_range" : {
"price" : {
"lte" : 4.99,
"gte" : 0
}
}
}
]
}
}
},
"from" : 0,
"size" : 18
}
'

Note:

  • I used "numeric_range" instead of "range" which is more efficient
    for fields that have many many terms (eg datetime). Your price
    field may only have a small number of terms, in which case, change
    it back to "range"

  • I used "gte" and "lte" rather than "to" and "from" because I think
    you want 0 to 4.99 INCLUSIVE. "from" would stop before 4.99.

clint


(system) #12