Elasticsearch DSL query from an SQL statement

Question

I'm new to Elasticsearch. I don't think I fully understand the concept of query and filters. In my case I just want to use filters as I don't want to use advance feature like scoring.

How would I convert the following SQL statement into elasticsearch query?

SELECT * FROM advertiser 
WHERE company like '%com%' 
AND sales_rep IN (1,2)

What I have so far:

curl -XGET 'localhost:9200/advertisers/advertiser/_search?pretty=true' -d ' 
 { 
     "query" : { 
         "bool" : { 
             "must" : { 
                 "wildcard" : { "company" : "*com*" } 
             } 
         } 
     }, 
     "size":1000000 

}'

How to I add the OR filters on sales_rep field?

Thanks

Andy · Accepted Answer · 2011-12-20 00:25:22Z

2

Add a "should" clause after your must clause. In a bool query, one or more should clauses must match by default. Actually, you can set the "minimum_number_should_match" to be any number, Check out the bool query docs.

For your case, this should work.

    "should" : [
        {
            "term" : { "sales_rep_id" : "1" }
        },
        {
            "term" : { "sales_rep_id" : "2" }
        }
    ],

The same concept works for bool filters. Just change "query" to "filter". The bool filter docs are here.

edited Dec 20, 2011 at 0:25

answered Dec 19, 2011 at 19:47

Andy

8,9498 gold badges48 silver badges69 bronze badges

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

Comments

sirjoe · Accepted Answer · 2016-03-01 10:40:54Z

0

I come across this post 4 years too late...

Anyways, perhaps the following code could be useful...

{
  "query": {
    "filtered": {
      "query": {
        "wildcard": {
          "company": "*com*"
        }
      },
      "filter": {
        "bool": {
          "should": [
            {
              "terms": {
                "sales_rep_id": [ "1", "2" ]
              }
            }
          ]
        }
      }
    }
  }
}

answered Mar 1, 2016 at 10:40

sirjoe

11 silver badge

Collectives™ on Stack Overflow

Elasticsearch DSL query from an SQL statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related