A query is not working, and I think it should

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

A query is not working, and I think it should

xavier lacot
Hello,

I discovered elasticsearch a few weeks ago and have found some time to
give it a try. I tried yesterday to use it to index some data that I
have in a database, and query this data using some constraints.

Say I have a database of products :

Products
 * id : integer
 * name : string
 * date : date
 * price : float
 * items_available : integer

I created the index "testindex"  using the mapping API, here is what I
find in the logs :

 Put mapping [product] with source [{
  "product" : {
    "type" : "object",
    "dynamic" : true,
    "enabled" : true,
    "pathType" : "full",
    "dateFormats" : [ "dateOptionalTime" ],
    "boostField" : {
      "name" : "_boost"
    },
    "properties" : {
      "id" : {
        "type" : "integer",
        "indexName" : "id",
        "index" : "not_analyzed",
        "store" : "yes",
        "termVector" : "no",
        "boost" : 1.0,
        "omitNorms" : true,
        "omitTermFreqAndPositions" : true,
        "precisionStep" : 4
      },
      "price" : {
        "type" : "float",
        "indexName" : "price",
        "index" : "not_analyzed",
        "store" : "yes",
        "termVector" : "no",
        "boost" : 1.0,
        "omitNorms" : true,
        "omitTermFreqAndPositions" : true,
        "precisionStep" : 4
      },
      "items_available" : {
        "type" : "integer",
        "indexName" : "items_available",
        "index" : "not_analyzed",
        "store" : "yes",
        "termVector" : "no",
        "boost" : 1.0,
        "omitNorms" : true,
        "omitTermFreqAndPositions" : true,
        "precisionStep" : 4
      },
      "name" : {
        "type" : "string",
        "indexName" : "name",
        "index" : "analyzed",
        "store" : "yes",
        "termVector" : "no",
        "boost" : 1.0,
        "omitNorms" : false,
        "omitTermFreqAndPositions" : false
      },
      "date" : {
        "type" : "date",
        "indexName" : "date",
        "index" : "not_analyzed",
        "store" : "yes",
        "termVector" : "no",
        "boost" : 1.0,
        "omitNorms" : true,
        "omitTermFreqAndPositions" : true,
        "precisionStep" : 4,
        "format" : "YYYY-MM-dd"
      }
    }
  }
}]

This is what I expect, so until here everything is fine. I index some
products :

curl -XPUT 'http://localhost:9200/testindex/product/1' -d
'{"name":"product1","date":"2010-03-01","price":12.7,"items_available":
2}'
curl -XPUT 'http://localhost:9200/testindex/product/2' -d
'{"name":"product2","date":"2010-03-01","price":30.2,"items_available":
7}'
curl -XPUT 'http://localhost:9200/testindex/product/3' -d
'{"name":"product3","date":"2010-03-01","price":50,"items_available":
6}'
curl -XPUT 'http://localhost:9200/testindex/product/4' -d
'{"name":"product4","date":"2010-03-01","price":69.2,"items_available":
5}'
curl -XPUT 'http://localhost:9200/testindex/product/5' -d
'{"name":"product5","date":"2010-03-01","price":85.4,"items_available":
10}'
curl -XPUT 'http://localhost:9200/testindex/product/6' -d
'{"name":"product6","date":"2010-03-01","price":100,"items_available":
1}'

If I try to retrieve the products in a certain price range, it works
well :

curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"query":{"range":{"price":{"from":30,"to":
60,"includeLower":true,"includeUpper":true}}}}'

My problem is when I try to retrieve the products in a certain price
range and with a certain name. Here is what I do :
curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"query":{"term":{"name":"product3"},"range":{"price":{"from":30,"to":
60,"includeLower":true,"includeUpper":true}}}}'

This returns an error :
{"_shards":{"total":5,"successful":0,"failed":5,"failures":
[{"index":"testindex","shardId":
3,"reason":"SearchParseException[[testindex][3]:
query[name:product3],from[-1],size[-1]: Parse Failure [Failed to parse
[{\"query\":{\"term\":{\"name\":\"product3\"},\"range\":{\"price\":
{\"from\":30,\"to\":60,\"includeLower\":true,\"includeUpper
\":true}}}}]]]; nested: SearchParseException[[testindex][3]:
query[name:product3],from[-1],size[-1]: Parse Failure [No parser for
element [price]]]; "},{"index":"testindex","shardId":
1,"reason":"SearchParseException[[testindex][1]:
query[name:product3],from[-1],size[-1]: Parse Failure [Failed to parse
[{\"query\":{\"term\":{\"name\":\"product3\"},\"range\":{\"price\":
{\"from\":30,\"to\":60,\"includeLower\":true,\"includeUpper
\":true}}}}]]]; nested: SearchParseException[[testindex][1]:
query[name:product3],from[-1],size[-1]: Parse Failure [No parser for
element [price]]]; "},{"reason":"Unknown"},{"reason":"Unknown"},
{"reason":"Unknown"}]},"hits":{"total":0,"hits":[]}}

My assumption is that it is not possible to mix term and range
queries. Therefore, I tried to use a filteredQuery :
curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"filteredQuery":{"query":{"term":{"name":"product3"}},"filter":
{"range":{"price":{"from":30,"to":60}}}}}'

Here in a more "readable" form :
{
  "filteredQuery" : {
    "query" : {
      "term" : { "name" : "product3" }
    },
    "filter" : {
      "range" : {
        "price" : { "from" : 30, "to" : 60 }
      }
    }
  }
}

And this wuary also laucnehs an exception, even if it is quite close
to the sample query in the documentation at
http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/#Filtered_Query

I there something I am doing wrong ? How should I write this (rather
simple) query ?

Regards and kudos for elasticsearch !

Xavier
Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

kimchy
Administrator
Regarding the first query that did not work, you should wrap the two queries in a bool query, with both queries (range and term) under a must clause. But, your second query, with the filtered one, is a much better fit since it will both perform better, and all you want is to filter based on a range, not take it into account when scoring.

The second query should have worked, can you maybe post the result that you get back (the exceptions you get) when you run it?

-shay.banon

On Thu, Mar 18, 2010 at 1:56 PM, xavier lacot <[hidden email]> wrote:
Hello,

I discovered elasticsearch a few weeks ago and have found some time to
give it a try. I tried yesterday to use it to index some data that I
have in a database, and query this data using some constraints.

Say I have a database of products :

Products
 * id : integer
 * name : string
 * date : date
 * price : float
 * items_available : integer

I created the index "testindex"  using the mapping API, here is what I
find in the logs :

 Put mapping [product] with source [{
 "product" : {
   "type" : "object",
   "dynamic" : true,
   "enabled" : true,
   "pathType" : "full",
   "dateFormats" : [ "dateOptionalTime" ],
   "boostField" : {
     "name" : "_boost"
   },
   "properties" : {
     "id" : {
       "type" : "integer",
       "indexName" : "id",
       "index" : "not_analyzed",
       "store" : "yes",
       "termVector" : "no",
       "boost" : 1.0,
       "omitNorms" : true,
       "omitTermFreqAndPositions" : true,
       "precisionStep" : 4
     },
     "price" : {
       "type" : "float",
       "indexName" : "price",
       "index" : "not_analyzed",
       "store" : "yes",
       "termVector" : "no",
       "boost" : 1.0,
       "omitNorms" : true,
       "omitTermFreqAndPositions" : true,
       "precisionStep" : 4
     },
     "items_available" : {
       "type" : "integer",
       "indexName" : "items_available",
       "index" : "not_analyzed",
       "store" : "yes",
       "termVector" : "no",
       "boost" : 1.0,
       "omitNorms" : true,
       "omitTermFreqAndPositions" : true,
       "precisionStep" : 4
     },
     "name" : {
       "type" : "string",
       "indexName" : "name",
       "index" : "analyzed",
       "store" : "yes",
       "termVector" : "no",
       "boost" : 1.0,
       "omitNorms" : false,
       "omitTermFreqAndPositions" : false
     },
     "date" : {
       "type" : "date",
       "indexName" : "date",
       "index" : "not_analyzed",
       "store" : "yes",
       "termVector" : "no",
       "boost" : 1.0,
       "omitNorms" : true,
       "omitTermFreqAndPositions" : true,
       "precisionStep" : 4,
       "format" : "YYYY-MM-dd"
     }
   }
 }
}]

This is what I expect, so until here everything is fine. I index some
products :

curl -XPUT 'http://localhost:9200/testindex/product/1' -d
'{"name":"product1","date":"2010-03-01","price":12.7,"items_available":
2}'
curl -XPUT 'http://localhost:9200/testindex/product/2' -d
'{"name":"product2","date":"2010-03-01","price":30.2,"items_available":
7}'
curl -XPUT 'http://localhost:9200/testindex/product/3' -d
'{"name":"product3","date":"2010-03-01","price":50,"items_available":
6}'
curl -XPUT 'http://localhost:9200/testindex/product/4' -d
'{"name":"product4","date":"2010-03-01","price":69.2,"items_available":
5}'
curl -XPUT 'http://localhost:9200/testindex/product/5' -d
'{"name":"product5","date":"2010-03-01","price":85.4,"items_available":
10}'
curl -XPUT 'http://localhost:9200/testindex/product/6' -d
'{"name":"product6","date":"2010-03-01","price":100,"items_available":
1}'

If I try to retrieve the products in a certain price range, it works
well :

curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"query":{"range":{"price":{"from":30,"to":
60,"includeLower":true,"includeUpper":true}}}}'

My problem is when I try to retrieve the products in a certain price
range and with a certain name. Here is what I do :
curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"query":{"term":{"name":"product3"},"range":{"price":{"from":30,"to":
60,"includeLower":true,"includeUpper":true}}}}'

This returns an error :
{"_shards":{"total":5,"successful":0,"failed":5,"failures":
[{"index":"testindex","shardId":
3,"reason":"SearchParseException[[testindex][3]:
query[name:product3],from[-1],size[-1]: Parse Failure [Failed to parse
[{\"query\":{\"term\":{\"name\":\"product3\"},\"range\":{\"price\":
{\"from\":30,\"to\":60,\"includeLower\":true,\"includeUpper
\":true}}}}]]]; nested: SearchParseException[[testindex][3]:
query[name:product3],from[-1],size[-1]: Parse Failure [No parser for
element [price]]]; "},{"index":"testindex","shardId":
1,"reason":"SearchParseException[[testindex][1]:
query[name:product3],from[-1],size[-1]: Parse Failure [Failed to parse
[{\"query\":{\"term\":{\"name\":\"product3\"},\"range\":{\"price\":
{\"from\":30,\"to\":60,\"includeLower\":true,\"includeUpper
\":true}}}}]]]; nested: SearchParseException[[testindex][1]:
query[name:product3],from[-1],size[-1]: Parse Failure [No parser for
element [price]]]; "},{"reason":"Unknown"},{"reason":"Unknown"},
{"reason":"Unknown"}]},"hits":{"total":0,"hits":[]}}

My assumption is that it is not possible to mix term and range
queries. Therefore, I tried to use a filteredQuery :
curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"filteredQuery":{"query":{"term":{"name":"product3"}},"filter":
{"range":{"price":{"from":30,"to":60}}}}}'

Here in a more "readable" form :
{
 "filteredQuery" : {
   "query" : {
     "term" : { "name" : "product3" }
   },
   "filter" : {
     "range" : {
       "price" : { "from" : 30, "to" : 60 }
     }
   }
 }
}

And this wuary also laucnehs an exception, even if it is quite close
to the sample query in the documentation at
http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/#Filtered_Query

I there something I am doing wrong ? How should I write this (rather
simple) query ?

Regards and kudos for elasticsearch !

Xavier

Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

Clinton Gormley
In reply to this post by xavier lacot


> Here in a more "readable" form :
> {
>   "filteredQuery" : {
>     "query" : {
>       "term" : { "name" : "product3" }
>     },
>     "filter" : {
>       "range" : {
>         "price" : { "from" : 30, "to" : 60 }
>       }
>     }
>   }
> }

I think this should be:

{ query:  
    {
       "filteredQuery" : {
         "query" : {
           "term" : { "name" : "product3" }
         },
         "filter" : {
           "range" : {
             "price" : { "from" : 30, "to" : 60 }
           }
         }
       }
     }
}

See my DSL reference here:
http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod

clint


--
Web Announcements Limited is a company registered in England and Wales,
with company number 05608868, with registered address at 10 Arvon Road,
London, N5 1PR.

Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

xavier lacot
In reply to this post by kimchy
Hi Shay,

Thanks for your answer.

here is the query and the result :

curl -XGET 'http://localhost:9200/testindex/product/_search' -d
'{"filteredQuery":{"query":{"term":{"name":"product3"}},"filter":{"range":{"price":{"from":30,"to":60}}}}}'

{"_shards":{"total":5,"successful":0,"failed":5,"failures":[{"index":"testindex","shardId":3,"reason":"SearchParseException[[testindex][3]:
query[null],from[-1],size[-1]: Parse Failure [Failed to parse
[{\"filteredQuery\":{\"query\":{\"term\":{\"name\":\"product3\"}},\"filter\":{\"range\":{\"price\":{\"from\":30,\"to\":60}}}}}]]];
nested: SearchParseException[[testindex][3]:
query[null],from[-1],size[-1]: Parse Failure [No parser for element
[filteredQuery]]];
"},{"index":"testindex","shardId":1,"reason":"SearchParseException[[testindex][1]:
query[null],from[-1],size[-1]: Parse Failure [Failed to parse
[{\"filteredQuery\":{\"query\":{\"term\":{\"name\":\"product3\"}},\"filter\":{\"range\":{\"price\":{\"from\":30,\"to\":60}}}}}]]];
nested: SearchParseException[[testindex][1]:
query[null],from[-1],size[-1]: Parse Failure [No parser for element
[filteredQuery]]];
"},{"reason":"Unknown"},{"reason":"Unknown"},{"reason":"Unknown"}]},"hits":{"total":0,"hits":[]}}

Best regards,

Xavier

2010/3/18 Shay Banon <[hidden email]>:

> Regarding the first query that did not work, you should wrap the two queries
> in a bool query, with both queries (range and term) under a must clause.
> But, your second query, with the filtered one, is a much better fit since it
> will both perform better, and all you want is to filter based on a range,
> not take it into account when scoring.
> The second query should have worked, can you maybe post the result that you
> get back (the exceptions you get) when you run it?
> -shay.banon
>
> On Thu, Mar 18, 2010 at 1:56 PM, xavier lacot <[hidden email]> wrote:
>>
>> Hello,
>>
>> I discovered elasticsearch a few weeks ago and have found some time to
>> give it a try. I tried yesterday to use it to index some data that I
>> have in a database, and query this data using some constraints.
>>
>> Say I have a database of products :
>>
>> Products
>>  * id : integer
>>  * name : string
>>  * date : date
>>  * price : float
>>  * items_available : integer
>>
>> I created the index "testindex"  using the mapping API, here is what I
>> find in the logs :
>>
>>  Put mapping [product] with source [{
>>  "product" : {
>>    "type" : "object",
>>    "dynamic" : true,
>>    "enabled" : true,
>>    "pathType" : "full",
>>    "dateFormats" : [ "dateOptionalTime" ],
>>    "boostField" : {
>>      "name" : "_boost"
>>    },
>>    "properties" : {
>>      "id" : {
>>        "type" : "integer",
>>        "indexName" : "id",
>>        "index" : "not_analyzed",
>>        "store" : "yes",
>>        "termVector" : "no",
>>        "boost" : 1.0,
>>        "omitNorms" : true,
>>        "omitTermFreqAndPositions" : true,
>>        "precisionStep" : 4
>>      },
>>      "price" : {
>>        "type" : "float",
>>        "indexName" : "price",
>>        "index" : "not_analyzed",
>>        "store" : "yes",
>>        "termVector" : "no",
>>        "boost" : 1.0,
>>        "omitNorms" : true,
>>        "omitTermFreqAndPositions" : true,
>>        "precisionStep" : 4
>>      },
>>      "items_available" : {
>>        "type" : "integer",
>>        "indexName" : "items_available",
>>        "index" : "not_analyzed",
>>        "store" : "yes",
>>        "termVector" : "no",
>>        "boost" : 1.0,
>>        "omitNorms" : true,
>>        "omitTermFreqAndPositions" : true,
>>        "precisionStep" : 4
>>      },
>>      "name" : {
>>        "type" : "string",
>>        "indexName" : "name",
>>        "index" : "analyzed",
>>        "store" : "yes",
>>        "termVector" : "no",
>>        "boost" : 1.0,
>>        "omitNorms" : false,
>>        "omitTermFreqAndPositions" : false
>>      },
>>      "date" : {
>>        "type" : "date",
>>        "indexName" : "date",
>>        "index" : "not_analyzed",
>>        "store" : "yes",
>>        "termVector" : "no",
>>        "boost" : 1.0,
>>        "omitNorms" : true,
>>        "omitTermFreqAndPositions" : true,
>>        "precisionStep" : 4,
>>        "format" : "YYYY-MM-dd"
>>      }
>>    }
>>  }
>> }]
>>
>> This is what I expect, so until here everything is fine. I index some
>> products :
>>
>> curl -XPUT 'http://localhost:9200/testindex/product/1' -d
>> '{"name":"product1","date":"2010-03-01","price":12.7,"items_available":
>> 2}'
>> curl -XPUT 'http://localhost:9200/testindex/product/2' -d
>> '{"name":"product2","date":"2010-03-01","price":30.2,"items_available":
>> 7}'
>> curl -XPUT 'http://localhost:9200/testindex/product/3' -d
>> '{"name":"product3","date":"2010-03-01","price":50,"items_available":
>> 6}'
>> curl -XPUT 'http://localhost:9200/testindex/product/4' -d
>> '{"name":"product4","date":"2010-03-01","price":69.2,"items_available":
>> 5}'
>> curl -XPUT 'http://localhost:9200/testindex/product/5' -d
>> '{"name":"product5","date":"2010-03-01","price":85.4,"items_available":
>> 10}'
>> curl -XPUT 'http://localhost:9200/testindex/product/6' -d
>> '{"name":"product6","date":"2010-03-01","price":100,"items_available":
>> 1}'
>>
>> If I try to retrieve the products in a certain price range, it works
>> well :
>>
>> curl -XGET 'http://localhost:9200/testindex/product/_search' -d
>> '{"query":{"range":{"price":{"from":30,"to":
>> 60,"includeLower":true,"includeUpper":true}}}}'
>>
>> My problem is when I try to retrieve the products in a certain price
>> range and with a certain name. Here is what I do :
>> curl -XGET 'http://localhost:9200/testindex/product/_search' -d
>> '{"query":{"term":{"name":"product3"},"range":{"price":{"from":30,"to":
>> 60,"includeLower":true,"includeUpper":true}}}}'
>>
>> This returns an error :
>> {"_shards":{"total":5,"successful":0,"failed":5,"failures":
>> [{"index":"testindex","shardId":
>> 3,"reason":"SearchParseException[[testindex][3]:
>> query[name:product3],from[-1],size[-1]: Parse Failure [Failed to parse
>> [{\"query\":{\"term\":{\"name\":\"product3\"},\"range\":{\"price\":
>> {\"from\":30,\"to\":60,\"includeLower\":true,\"includeUpper
>> \":true}}}}]]]; nested: SearchParseException[[testindex][3]:
>> query[name:product3],from[-1],size[-1]: Parse Failure [No parser for
>> element [price]]]; "},{"index":"testindex","shardId":
>> 1,"reason":"SearchParseException[[testindex][1]:
>> query[name:product3],from[-1],size[-1]: Parse Failure [Failed to parse
>> [{\"query\":{\"term\":{\"name\":\"product3\"},\"range\":{\"price\":
>> {\"from\":30,\"to\":60,\"includeLower\":true,\"includeUpper
>> \":true}}}}]]]; nested: SearchParseException[[testindex][1]:
>> query[name:product3],from[-1],size[-1]: Parse Failure [No parser for
>> element [price]]]; "},{"reason":"Unknown"},{"reason":"Unknown"},
>> {"reason":"Unknown"}]},"hits":{"total":0,"hits":[]}}
>>
>> My assumption is that it is not possible to mix term and range
>> queries. Therefore, I tried to use a filteredQuery :
>> curl -XGET 'http://localhost:9200/testindex/product/_search' -d
>> '{"filteredQuery":{"query":{"term":{"name":"product3"}},"filter":
>> {"range":{"price":{"from":30,"to":60}}}}}'
>>
>> Here in a more "readable" form :
>> {
>>  "filteredQuery" : {
>>    "query" : {
>>      "term" : { "name" : "product3" }
>>    },
>>    "filter" : {
>>      "range" : {
>>        "price" : { "from" : 30, "to" : 60 }
>>      }
>>    }
>>  }
>> }
>>
>> And this wuary also laucnehs an exception, even if it is quite close
>> to the sample query in the documentation at
>>
>> http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/#Filtered_Query
>>
>> I there something I am doing wrong ? How should I write this (rather
>> simple) query ?
>>
>> Regards and kudos for elasticsearch !
>>
>> Xavier
>
>



--
Xavier Lacot
[hidden email]
http://lacot.org/
Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

kimchy
Administrator
In reply to this post by Clinton Gormley
Right!, I missed it :)

On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]> wrote:


> Here in a more "readable" form :
> {
>   "filteredQuery" : {
>     "query" : {
>       "term" : { "name" : "product3" }
>     },
>     "filter" : {
>       "range" : {
>         "price" : { "from" : 30, "to" : 60 }
>       }
>     }
>   }
> }

I think this should be:

{ query:
   {
      "filteredQuery" : {
        "query" : {
          "term" : { "name" : "product3" }
        },
        "filter" : {
          "range" : {
            "price" : { "from" : 30, "to" : 60 }
          }
        }
      }
    }
}

See my DSL reference here:
http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod

clint


--
Web Announcements Limited is a company registered in England and Wales,
with company number 05608868, with registered address at 10 Arvon Road,
London, N5 1PR.


Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

xavier lacot
Oh great! :-) Thanks both for the help !

Xavier

2010/3/18 Shay Banon <[hidden email]>:

> Right!, I missed it :)
>
> On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]>
> wrote:
>>
>>
>> > Here in a more "readable" form :
>> > {
>> >   "filteredQuery" : {
>> >     "query" : {
>> >       "term" : { "name" : "product3" }
>> >     },
>> >     "filter" : {
>> >       "range" : {
>> >         "price" : { "from" : 30, "to" : 60 }
>> >       }
>> >     }
>> >   }
>> > }
>>
>> I think this should be:
>>
>> { query:
>>    {
>>       "filteredQuery" : {
>>         "query" : {
>>           "term" : { "name" : "product3" }
>>         },
>>         "filter" : {
>>           "range" : {
>>             "price" : { "from" : 30, "to" : 60 }
>>           }
>>         }
>>       }
>>     }
>> }
>>
>> See my DSL reference here:
>>
>> http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod
>>
>> clint
>>
>>
>> --
>> Web Announcements Limited is a company registered in England and Wales,
>> with company number 05608868, with registered address at 10 Arvon Road,
>> London, N5 1PR.
>>
>
>



--
Xavier Lacot
[hidden email]
http://lacot.org/
Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

xavier lacot
Hello,

The last query proposed by Clinton worked well, as expected. But now, I want to make more complex queries, with filters on several terms.

For instance, I want to search products based on the product name, the brand name, the type of availability ("available" or "not available"), the city where the product is (so, a city_name and eventually a zip_code), and some other similar criteria. I tried for instance the following request, but it always returns an empty resultset, event if there should have been valid results. Here is what I tried :

{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "zip_code" : 12345, "brand" : "testbrand" }
          }
        }
      }
    }
  }
}

--> results are only filtered on zip code, not on brand.

{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "brand" : "testbrand" },
              "term" : { "zip_code" : 12345 }
          }
        }
      }
    }
  }
}

-> QueryParsingException[[myindexname] No json query parser registered for [zip_code]]

How do I express a combination of filters? The example only shows restrictions with two filters, but I will have to build queries with 5 or more restrictions. Is there a trivial solution I have not seen in the documentation? I searched well but could not find examples for complex queries like these.

+ Is t possible to have boolean filters with several disjoind "should" queries ? I want to express in the same query something like : "the city is *** OR the zip_code is *****", and "the type of product is TYPE_1 or the type of product is TYPE_2 or the type of product is TYPE_3". How would you write these?

Regards + bravos for ES, it's a pleasure to use it!

Xavier


2010/3/18 Xavier Lacot <[hidden email]>
Oh great! :-) Thanks both for the help !

Xavier

2010/3/18 Shay Banon <[hidden email]>:
> Right!, I missed it :)
>
> On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]>
> wrote:
>>
>>
>> > Here in a more "readable" form :
>> > {
>> >   "filteredQuery" : {
>> >     "query" : {
>> >       "term" : { "name" : "product3" }
>> >     },
>> >     "filter" : {
>> >       "range" : {
>> >         "price" : { "from" : 30, "to" : 60 }
>> >       }
>> >     }
>> >   }
>> > }
>>
>> I think this should be:
>>
>> { query:
>>    {
>>       "filteredQuery" : {
>>         "query" : {
>>           "term" : { "name" : "product3" }
>>         },
>>         "filter" : {
>>           "range" : {
>>             "price" : { "from" : 30, "to" : 60 }
>>           }
>>         }
>>       }
>>     }
>> }
>>
>> See my DSL reference here:
>>
>> http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod
>>
>> clint
>>
>>
>> --
>> Web Announcements Limited is a company registered in England and Wales,
>> with company number 05608868, with registered address at 10 Arvon Road,
>> London, N5 1PR.
>>
>
>






--
Xavier Lacot
[hidden email]
http://lacot.org/
Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

kimchy
Administrator
Hi,

   Each clase in the bool filter can accept a (json) array of filters. Have a look here: http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/bool_filter/ in which the should accepts an array of two filters. You can use the same syntax on the must/mustNot clauses as well.

-shay.banon

On Thu, Apr 1, 2010 at 9:31 AM, Xavier Lacot <[hidden email]> wrote:
Hello,

The last query proposed by Clinton worked well, as expected. But now, I want to make more complex queries, with filters on several terms.

For instance, I want to search products based on the product name, the brand name, the type of availability ("available" or "not available"), the city where the product is (so, a city_name and eventually a zip_code), and some other similar criteria. I tried for instance the following request, but it always returns an empty resultset, event if there should have been valid results. Here is what I tried :


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "zip_code" : 12345, "brand" : "testbrand" }
          }
        }
      }
    }
  }
}

--> results are only filtered on zip code, not on brand.


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "brand" : "testbrand" },
              "term" : { "zip_code" : 12345 }
          }
        }
      }
    }
  }
}

-> QueryParsingException[[myindexname] No json query parser registered for [zip_code]]

How do I express a combination of filters? The example only shows restrictions with two filters, but I will have to build queries with 5 or more restrictions. Is there a trivial solution I have not seen in the documentation? I searched well but could not find examples for complex queries like these.

+ Is t possible to have boolean filters with several disjoind "should" queries ? I want to express in the same query something like : "the city is *** OR the zip_code is *****", and "the type of product is TYPE_1 or the type of product is TYPE_2 or the type of product is TYPE_3". How would you write these?

Regards + bravos for ES, it's a pleasure to use it!

Xavier


2010/3/18 Xavier Lacot <[hidden email]>

Oh great! :-) Thanks both for the help !

Xavier

2010/3/18 Shay Banon <[hidden email]>:
> Right!, I missed it :)
>
> On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]>
> wrote:
>>
>>
>> > Here in a more "readable" form :
>> > {
>> >   "filteredQuery" : {
>> >     "query" : {
>> >       "term" : { "name" : "product3" }
>> >     },
>> >     "filter" : {
>> >       "range" : {
>> >         "price" : { "from" : 30, "to" : 60 }
>> >       }
>> >     }
>> >   }
>> > }
>>
>> I think this should be:
>>
>> { query:
>>    {
>>       "filteredQuery" : {
>>         "query" : {
>>           "term" : { "name" : "product3" }
>>         },
>>         "filter" : {
>>           "range" : {
>>             "price" : { "from" : 30, "to" : 60 }
>>           }
>>         }
>>       }
>>     }
>> }
>>
>> See my DSL reference here:
>>
>> http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod
>>
>> clint
>>
>>
>> --
>> Web Announcements Limited is a company registered in England and Wales,
>> with company number 05608868, with registered address at 10 Arvon Road,
>> London, N5 1PR.
>>
>
>




Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

xavier lacot
Hi Shay,

Thanks for the answer, I did not notice the array in the sample. Maybe should it be worth a sentence underlining this point?

There is however still a point that I am missing : is it possible to have boolean filters with several disjoint "should" queries ? I want to express in the same query something like: "the city is *** OR the zip_code is *****" AND "the type of product is TYPE_1 OR the type of product is TYPE_2". How would you write this?

I tried the following, but it did not work:

"filter" : {
  "bool" : {
    "should" : [
        {
            "term" : { "city" : "city1" }
        },
        {
            "term" : { "zip_code" : "012345" }
        }
    ],
    "should" : [
        {
            "term" : { "product" : "product1" }
        },
        {
            "term" : { "product" : "product2" }
        }
    ]
  }
}

Then, I tried to write "should" boolean queries nested into a "must" one. It works as expected, but I am not sure if it is the most concise and efficient syntax?

"filter" : {
  "bool" : {
    "must" : [
      {
        "bool" : {
          "should" : [
            {
              "term" : { "city" : "city1" }
            },
            {
              "term" : { "zip_code" : "012345" }
            }
          ]
        }
      },
      {
        "bool" : {
          "should" : [
            {
              "term" : { "product" : "product1" }
            },
            {
              "term" : { "product" : "product2" }
            }
          ]
        }
      }
    ]
  }
}

kudos + cheers,

Xavier


2010/4/1 Shay Banon <[hidden email]>
Hi,

   Each clase in the bool filter can accept a (json) array of filters. Have a look here: http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/bool_filter/ in which the should accepts an array of two filters. You can use the same syntax on the must/mustNot clauses as well.

-shay.banon

On Thu, Apr 1, 2010 at 9:31 AM, Xavier Lacot <[hidden email]> wrote:
Hello,

The last query proposed by Clinton worked well, as expected. But now, I want to make more complex queries, with filters on several terms.

For instance, I want to search products based on the product name, the brand name, the type of availability ("available" or "not available"), the city where the product is (so, a city_name and eventually a zip_code), and some other similar criteria. I tried for instance the following request, but it always returns an empty resultset, event if there should have been valid results. Here is what I tried :


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "zip_code" : 12345, "brand" : "testbrand" }
          }
        }
      }
    }
  }
}

--> results are only filtered on zip code, not on brand.


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "brand" : "testbrand" },
              "term" : { "zip_code" : 12345 }
          }
        }
      }
    }
  }
}

-> QueryParsingException[[myindexname] No json query parser registered for [zip_code]]

How do I express a combination of filters? The example only shows restrictions with two filters, but I will have to build queries with 5 or more restrictions. Is there a trivial solution I have not seen in the documentation? I searched well but could not find examples for complex queries like these.

+ Is t possible to have boolean filters with several disjoind "should" queries ? I want to express in the same query something like : "the city is *** OR the zip_code is *****", and "the type of product is TYPE_1 or the type of product is TYPE_2 or the type of product is TYPE_3". How would you write these?

Regards + bravos for ES, it's a pleasure to use it!

Xavier


2010/3/18 Xavier Lacot <[hidden email]>

Oh great! :-) Thanks both for the help !

Xavier

2010/3/18 Shay Banon <[hidden email]>:
> Right!, I missed it :)
>
> On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]>
> wrote:
>>
>>
>> > Here in a more "readable" form :
>> > {
>> >   "filteredQuery" : {
>> >     "query" : {
>> >       "term" : { "name" : "product3" }
>> >     },
>> >     "filter" : {
>> >       "range" : {
>> >         "price" : { "from" : 30, "to" : 60 }
>> >       }
>> >     }
>> >   }
>> > }
>>
>> I think this should be:
>>
>> { query:
>>    {
>>       "filteredQuery" : {
>>         "query" : {
>>           "term" : { "name" : "product3" }
>>         },
>>         "filter" : {
>>           "range" : {
>>             "price" : { "from" : 30, "to" : 60 }
>>           }
>>         }
>>       }
>>     }
>> }
>>
>> See my DSL reference here:
>>
>> http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod
>>
>> clint
>>
>>
>> --
>> Web Announcements Limited is a company registered in England and Wales,
>> with company number 05608868, with registered address at 10 Arvon Road,
>> London, N5 1PR.
>>
>
>







--
Xavier Lacot
[hidden email]
http://lacot.org/
Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

kimchy
Administrator
The should/must/mustNot elements in the bool filter accepts *any* other filter, which can be another bool filter. So, you can construct a boolfilter of two must clauses, each containing another bool filter. Same thing, by the way, applied to the bool query.

-shay.banon

On Fri, Apr 2, 2010 at 12:54 AM, Xavier Lacot <[hidden email]> wrote:
Hi Shay,

Thanks for the answer, I did not notice the array in the sample. Maybe should it be worth a sentence underlining this point?

There is however still a point that I am missing : is it possible to have boolean filters with several disjoint "should" queries ? I want to express in the same query something like: "the city is *** OR the zip_code is *****" AND "the type of product is TYPE_1 OR the type of product is TYPE_2". How would you write this?

I tried the following, but it did not work:

"filter" : {
  "bool" : {
    "should" : [
        {
            "term" : { "city" : "city1" }
        },
        {
            "term" : { "zip_code" : "012345" }
        }
    ],
    "should" : [
        {
            "term" : { "product" : "product1" }
        },
        {
            "term" : { "product" : "product2" }
        }
    ]
  }
}

Then, I tried to write "should" boolean queries nested into a "must" one. It works as expected, but I am not sure if it is the most concise and efficient syntax?

"filter" : {
  "bool" : {
    "must" : [
      {
        "bool" : {
          "should" : [
            {
              "term" : { "city" : "city1" }
            },
            {
              "term" : { "zip_code" : "012345" }
            }
          ]
        }
      },
      {
        "bool" : {
          "should" : [
            {
              "term" : { "product" : "product1" }
            },
            {
              "term" : { "product" : "product2" }
            }
          ]
        }
      }
    ]
  }
}

kudos + cheers,

Xavier


2010/4/1 Shay Banon <[hidden email]>

Hi,

   Each clase in the bool filter can accept a (json) array of filters. Have a look here: http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/bool_filter/ in which the should accepts an array of two filters. You can use the same syntax on the must/mustNot clauses as well.

-shay.banon

On Thu, Apr 1, 2010 at 9:31 AM, Xavier Lacot <[hidden email]> wrote:
Hello,

The last query proposed by Clinton worked well, as expected. But now, I want to make more complex queries, with filters on several terms.

For instance, I want to search products based on the product name, the brand name, the type of availability ("available" or "not available"), the city where the product is (so, a city_name and eventually a zip_code), and some other similar criteria. I tried for instance the following request, but it always returns an empty resultset, event if there should have been valid results. Here is what I tried :


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "zip_code" : 12345, "brand" : "testbrand" }
          }
        }
      }
    }
  }
}

--> results are only filtered on zip code, not on brand.


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "brand" : "testbrand" },
              "term" : { "zip_code" : 12345 }
          }
        }
      }
    }
  }
}

-> QueryParsingException[[myindexname] No json query parser registered for [zip_code]]

How do I express a combination of filters? The example only shows restrictions with two filters, but I will have to build queries with 5 or more restrictions. Is there a trivial solution I have not seen in the documentation? I searched well but could not find examples for complex queries like these.

+ Is t possible to have boolean filters with several disjoind "should" queries ? I want to express in the same query something like : "the city is *** OR the zip_code is *****", and "the type of product is TYPE_1 or the type of product is TYPE_2 or the type of product is TYPE_3". How would you write these?

Regards + bravos for ES, it's a pleasure to use it!

Xavier


2010/3/18 Xavier Lacot <[hidden email]>

Oh great! :-) Thanks both for the help !

Xavier

2010/3/18 Shay Banon <[hidden email]>:
> Right!, I missed it :)
>
> On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]>
> wrote:
>>
>>
>> > Here in a more "readable" form :
>> > {
>> >   "filteredQuery" : {
>> >     "query" : {
>> >       "term" : { "name" : "product3" }
>> >     },
>> >     "filter" : {
>> >       "range" : {
>> >         "price" : { "from" : 30, "to" : 60 }
>> >       }
>> >     }
>> >   }
>> > }
>>
>> I think this should be:
>>
>> { query:
>>    {
>>       "filteredQuery" : {
>>         "query" : {
>>           "term" : { "name" : "product3" }
>>         },
>>         "filter" : {
>>           "range" : {
>>             "price" : { "from" : 30, "to" : 60 }
>>           }
>>         }
>>       }
>>     }
>> }
>>
>> See my DSL reference here:
>>
>> http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod
>>
>> clint
>>
>>
>> --
>> Web Announcements Limited is a company registered in England and Wales,
>> with company number 05608868, with registered address at 10 Arvon Road,
>> London, N5 1PR.
>>
>
>







--

Reply | Threaded
Open this post in threaded view
|

Re: A query is not working, and I think it should

xavier lacot
Great! Just wanted to be sure. Thanks a lot.

Xavier

2010/4/2 Shay Banon <[hidden email]>
The should/must/mustNot elements in the bool filter accepts *any* other filter, which can be another bool filter. So, you can construct a boolfilter of two must clauses, each containing another bool filter. Same thing, by the way, applied to the bool query.

-shay.banon


On Fri, Apr 2, 2010 at 12:54 AM, Xavier Lacot <[hidden email]> wrote:
Hi Shay,

Thanks for the answer, I did not notice the array in the sample. Maybe should it be worth a sentence underlining this point?

There is however still a point that I am missing : is it possible to have boolean filters with several disjoint "should" queries ? I want to express in the same query something like: "the city is *** OR the zip_code is *****" AND "the type of product is TYPE_1 OR the type of product is TYPE_2". How would you write this?

I tried the following, but it did not work:

"filter" : {
  "bool" : {
    "should" : [
        {
            "term" : { "city" : "city1" }
        },
        {
            "term" : { "zip_code" : "012345" }
        }
    ],
    "should" : [
        {
            "term" : { "product" : "product1" }
        },
        {
            "term" : { "product" : "product2" }
        }
    ]
  }
}

Then, I tried to write "should" boolean queries nested into a "must" one. It works as expected, but I am not sure if it is the most concise and efficient syntax?

"filter" : {
  "bool" : {
    "must" : [
      {
        "bool" : {
          "should" : [
            {
              "term" : { "city" : "city1" }
            },
            {
              "term" : { "zip_code" : "012345" }
            }
          ]
        }
      },
      {
        "bool" : {
          "should" : [
            {
              "term" : { "product" : "product1" }
            },
            {
              "term" : { "product" : "product2" }
            }
          ]
        }
      }
    ]
  }
}

kudos + cheers,

Xavier


2010/4/1 Shay Banon <[hidden email]>

Hi,

   Each clase in the bool filter can accept a (json) array of filters. Have a look here: http://www.elasticsearch.com/docs/elasticsearch/rest_api/query_dsl/bool_filter/ in which the should accepts an array of two filters. You can use the same syntax on the must/mustNot clauses as well.

-shay.banon

On Thu, Apr 1, 2010 at 9:31 AM, Xavier Lacot <[hidden email]> wrote:
Hello,

The last query proposed by Clinton worked well, as expected. But now, I want to make more complex queries, with filters on several terms.

For instance, I want to search products based on the product name, the brand name, the type of availability ("available" or "not available"), the city where the product is (so, a city_name and eventually a zip_code), and some other similar criteria. I tried for instance the following request, but it always returns an empty resultset, event if there should have been valid results. Here is what I tried :


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "zip_code" : 12345, "brand" : "testbrand" }
          }
        }
      }
    }
  }
}

--> results are only filtered on zip code, not on brand.


{ query:
  {
    "filteredQuery" : {
      "query" : {
        "term" : { "name" : "product3" }
      },
      "filter" : {
        "bool" : {
          "must" : {
              "term" : { "brand" : "testbrand" },
              "term" : { "zip_code" : 12345 }
          }
        }
      }
    }
  }
}

-> QueryParsingException[[myindexname] No json query parser registered for [zip_code]]

How do I express a combination of filters? The example only shows restrictions with two filters, but I will have to build queries with 5 or more restrictions. Is there a trivial solution I have not seen in the documentation? I searched well but could not find examples for complex queries like these.

+ Is t possible to have boolean filters with several disjoind "should" queries ? I want to express in the same query something like : "the city is *** OR the zip_code is *****", and "the type of product is TYPE_1 or the type of product is TYPE_2 or the type of product is TYPE_3". How would you write these?

Regards + bravos for ES, it's a pleasure to use it!

Xavier


2010/3/18 Xavier Lacot <[hidden email]>

Oh great! :-) Thanks both for the help !

Xavier

2010/3/18 Shay Banon <[hidden email]>:
> Right!, I missed it :)
>
> On Thu, Mar 18, 2010 at 4:16 PM, Clinton Gormley <[hidden email]>
> wrote:
>>
>>
>> > Here in a more "readable" form :
>> > {
>> >   "filteredQuery" : {
>> >     "query" : {
>> >       "term" : { "name" : "product3" }
>> >     },
>> >     "filter" : {
>> >       "range" : {
>> >         "price" : { "from" : 30, "to" : 60 }
>> >       }
>> >     }
>> >   }
>> > }
>>
>> I think this should be:
>>
>> { query:
>>    {
>>       "filteredQuery" : {
>>         "query" : {
>>           "term" : { "name" : "product3" }
>>         },
>>         "filter" : {
>>           "range" : {
>>             "price" : { "from" : 30, "to" : 60 }
>>           }
>>         }
>>       }
>>     }
>> }
>>
>> See my DSL reference here:
>>
>> http://search.cpan.org/~drtech/ElasticSearch-0.05/lib/ElasticSearch/QueryDSL.pod
>>
>> clint
>>
>>
>> --
>> Web Announcements Limited is a company registered in England and Wales,
>> with company number 05608868, with registered address at 10 Arvon Road,
>> London, N5 1PR.
>>
>
>







--




--
Xavier Lacot
[hidden email]
http://lacot.org/