Help with Query (From SQL to Elastic query)


(Nemo) #1

How can I write elastic query for below SQL query

Select from myindex where data > last_one_dat and (property1 >= 0 OR property2 >=0 )

data is in milliseconds.

Regards,


(Nik Everett) #2

You can do this part with a script query but it'll be slow because it has to load both fields and check them. If the rest of the query is selective enough it'll still be fast enough though.

Use a bool query for this with two range queries in the should clause.


(Nemo) #3

Pardon me for missing some information previously. Here I need to skip the document when property1 and property2 are 0. I need to pick if either one or both is present. But the above query is giving me the document where property1 and property2 is 0


(Wanglifengwf) #4

you can do like this:

POST /index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "range": {
                  "property1": {
                    "gte": 0
                  }
                }
              },
              {
                "range": {
                  "property2": {
                    "gte": 0
                  }
                }
              }
            ]
          }
        },
        {
          "range": {
            "data": {
              "gte": last_one_dat
            }
          }
        }
      ]
    }
  }
}

use script:

POST index/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": {
            "inline": "(doc['property1'].value>=0 | doc['property2'].value>0) & doc['date']> last_one_dat"
          }
        }
      }
    }
  }
}

(Nemo) #5

Thank you very much for the reply. I am able to use script but it works only after escaping the quotes
doc['"'"'property1'"'"'].value>=0


(Wanglifengwf) #6

like this:

"(doc['"+property1+"'].value>=0"

(Nik Everett) #7

And you can use "gt" instead of "gte" if you don't want to include the
lower bound.


(system) #8