Combining Filters in ElasticSearch

Question

This is the basic SQL query which I want to convert into elasticsearch query,

SELECT product
FROM   products
WHERE  (price = 200 OR ID = "101")
  AND  (price != 300)

This is a query in elasticsearch that I have tried:

GET /my_store/my_product_items/_search

{
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "must" : [
                 { "term" : {"price" : 200}}, 
                 { "term" : {"productID" : "101"}} 
              ],
              "must_not" : {
                 "term" : {"price" : 300} 
              }
           }
         }
      }
   }
}

But it didn't give the same output as per SQL query. Do support.

TIA. :)

Note: you also can check out the Elasticsearch 6.3 release, now (June 2018) with native SQL support. — VonC
– VonC, Commented Jun 16, 2018 at 16:10

Val · Accepted Answer · 2017-05-08 05:28:29Z

2

Good start! You simply need to change must with should and you're good:

GET /my_store/my_product_items/_search

{
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "minimum_should_match": 1,         <--- add this
              "should" : [                       <--- change this
                 { "term" : {"price" : 200}}, 
                 { "term" : {"productID" : "101"}} 
              ],
              "must_not" : {
                 "term" : {"price" : 300} 
              }
           }
         }
      }
   }
}

answered May 8, 2017 at 5:28

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Unknown_Coder Over a year ago

What "minimum_should_match" is indicate ?

Val Over a year ago

That at least one clause of the should clauses should match, which is exactly what an OR is all about

Collectives™ on Stack Overflow

Combining Filters in ElasticSearch

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related