# Help with Query (From SQL to Elastic query)

**URL:** <https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881>\
**Category:** Elasticsearch\
**Created:** [October 13, 2016, 2:08am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881 "2016-10-13T02:08:14Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pradeep\_Gowda](https://avatars.discourse-cdn.com/v4/letter/p/ebca7d/32.png) [@Pradeep\_Gowda](https://discuss.elastic.co/u/Pradeep_Gowda)\
**Post date:** [October 13, 2016, 2:08am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/1 "2016-10-13T02:08:15Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [October 13, 2016, 3:28am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/2 "2016-10-13T03:28:31Z")

</div>

> [@Pradeep\_Gowda](#):
>
> data \> last\_one\_dat

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.

> [@Pradeep\_Gowda](#):
>
> property1 \>= 0 OR property2 \>=0

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

---

<div class="post-metadata">

**Author:** ![Pradeep\_Gowda](https://avatars.discourse-cdn.com/v4/letter/p/ebca7d/32.png) [@Pradeep\_Gowda](https://discuss.elastic.co/u/Pradeep_Gowda)\
**Post date:** [October 13, 2016, 3:38am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/3 "2016-10-13T03:38:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![wanglifengwf](https://avatars.discourse-cdn.com/v4/letter/w/41988e/32.png) [@wanglifengwf](https://discuss.elastic.co/u/wanglifengwf)\
**Post date:** [October 13, 2016, 5:39am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/4 "2016-10-13T05:39:16Z")

</div>

**you can do like this:**

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

```

**use script:**

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

```

---

<div class="post-metadata">

**Author:** ![Pradeep\_Gowda](https://avatars.discourse-cdn.com/v4/letter/p/ebca7d/32.png) [@Pradeep\_Gowda](https://discuss.elastic.co/u/Pradeep_Gowda)\
**Post date:** [October 13, 2016, 7:22am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/5 "2016-10-13T07:22:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![wanglifengwf](https://avatars.discourse-cdn.com/v4/letter/w/41988e/32.png) [@wanglifengwf](https://discuss.elastic.co/u/wanglifengwf)\
**Post date:** [October 13, 2016, 8:04am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/6 "2016-10-13T08:04:05Z")

</div>

like this:

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

```

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [October 13, 2016, 11:44am UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/7 "2016-10-13T11:44:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:12pm UTC](https://discuss.elastic.co/t/help-with-query-from-sql-to-elastic-query/62881/8 "2017-07-05T22:12:38Z")

</div>