# SQL Query convert to ES query

**URL:** <https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507>\
**Category:** Elasticsearch\
**Created:** [May 7, 2020, 10:11am UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507 "2020-05-07T10:11:14Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ramesh\_balasubramani](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ramesh_balasubramani/32/53173_2.png) [@Ramesh\_balasubramani](https://discuss.elastic.co/u/Ramesh_balasubramani)\
**Post date:** [May 7, 2020, 10:11am UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507/1 "2020-05-07T10:11:14Z")

</div>

Hi Guys,

```
(message == "Accpted" OR message == "Password)
OR
(Hostname == "SSDESK-003)
AND
(( src_ip != 10.100.7.102) AND (engine_log_id == "7"))

```

How to convert above query to ES Query?

Any quick help would be appreciated!

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 7, 2020, 4:39pm UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507/2 "2020-05-07T16:39:47Z")

</div>

Check the [ES SQL translate API](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html).  
Keep in mind you need to enclose your string literals in single quotes:

```auto
SELECT * FROM test WHERE (message = 'Accepted' OR message = 'Password') OR (Hostname = 'SSDESK-003') AND ((src_ip != '10.100.7.102') AND (engine_log_id = 7))

```

---

<div class="post-metadata">

**Author:** ![Ramesh\_balasubramani](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ramesh_balasubramani/32/53173_2.png) [@Ramesh\_balasubramani](https://discuss.elastic.co/u/Ramesh_balasubramani)\
**Post date:** [May 7, 2020, 6:07pm UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507/3 "2020-05-07T18:07:46Z")

</div>

This solution may not help us as we don't have x-pack.

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 8, 2020, 11:11am UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507/4 "2020-05-08T11:11:21Z")

</div>

```auto
{
    "size": 1000,
    "query": {
        "bool": {
            "should": [
                {
                    "bool": {
                        "should": [
                            {
                                "term": {
                                    "message.keyword": {
                                        "value": "Accepted",
                                        "boost": 1.0
                                    }
                                }
                            },
                            {
                                "term": {
                                    "message.keyword": {
                                        "value": "Password",
                                        "boost": 1.0
                                    }
                                }
                            }
                        ],
                        "boost": 1.0
                    }
                },
                {
                    "bool": {
                        "must": [
                            {
                                "term": {
                                    "Hostname.keyword": {
                                        "value": "SSDESK-003",
                                        "boost": 1.0
                                    }
                                }
                            },
                            {
                                "bool": {
                                    "must": [
                                        {
                                            "bool": {
                                                "must_not": [
                                                    {
                                                        "term": {
                                                            "src_ip": {
                                                                "value": "10.100.7.102",
                                                                "boost": 1.0
                                                            }
                                                        }
                                                    }
                                                ],
                                                "boost": 1.0
                                            }
                                        },
                                        {
                                            "term": {
                                                "engine_log_id": {
                                                    "value": 7,
                                                    "boost": 1.0
                                                }
                                            }
                                        }
                                    ],
                                    "boost": 1.0
                                }
                            }
                        ],
                        "boost": 1.0
                    }
                }
            ],
            "boost": 1.0
        }
    }
}

```

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 25, 2020, 2:15pm UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507/6 "2020-05-25T14:15:50Z")

</div>

I don't get what do you mean by this image, but the query I've pasted before, uses the bool-should for OR and bool-must for AND and it's quite visible how to match values for a field.  
Please refer to docs for [term-level queries](https://www.elastic.co/guide/en/elasticsearch/reference/current/term-level-queries.html) and [compound queries](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html) for more info.

---

<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:** [June 22, 2020, 2:15pm UTC](https://discuss.elastic.co/t/sql-query-convert-to-es-query/231507/7 "2020-06-22T14:15:50Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.