0

The query below is what I would like to construct using elasticsearch-dsl-py, but I do not know how to do it.

GET /my_index/_search
{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "should": [
            {
              "term": {
                "status": "a"
              },
              "term": {
                "status": "b"
              },
              "term": {
                "status": "c"
              }
            }
          ]
        }
      }
    }
  }
}

I just want to execute a query like below in SQL format

select * from my_index where status in ("a","b","c")

Using elasticsearch-dsl-py, this is as close as I can get, but it is not the same.

class MyIndex(Document):

    status = Keyword() / Text()
    
    

MyIndex.search().filter('should', status=["a","b","c"])
Kaushal Prajapati
  • 73
  • 1
  • 12

1 Answers1

2

Another way of doing this is by using the terms query with an array as each array element is implicitly ORed. Also, I'm not sure which version of ES you're running, but just know that filtered has been replaced by bool a long time ago in version 5. So your query can be rewritten like this:

GET /my_index/_search
{
  "query": {
    "bool": {
      "filter": {
         "terms": {
           "status": ["a", "b", "c"]
         }
      }
    }
  }
}

Using elasticsearch-dsl-py, this translates to:

s = Search()
s = s.filter('terms', status=['a', 'b', 'c'])
Val
  • 207,596
  • 13
  • 358
  • 360
  • This answer worked for me but I fail to see the difference with `{"query": {"terms": {"status": ["a", "b", "c"]}}}`, could you tell please? – ciurlaro Nov 23 '21 at 13:35
  • @ciurlaro not sure what you mean – Val Nov 23 '21 at 14:29
  • is there any logical difference between the query you wrote and the one that I wrote? I was wondering why you put also put `bool` and `filter` – ciurlaro Nov 23 '21 at 16:16
  • 1
    The difference is that without `bool/filter`, scoring kicks in and when you're doing exact matching like is the case in this question, it's useless and increases the response time unnecessarily – Val Nov 23 '21 at 16:30
  • That was a clever observation. Thanks. – ciurlaro Nov 24 '21 at 10:06