# SQL to ES Query

**URL:** <https://discuss.elastic.co/t/sql-to-es-query/128821>\
**Category:** Elasticsearch\
**Created:** [April 20, 2018, 8:01am UTC](https://discuss.elastic.co/t/sql-to-es-query/128821 "2018-04-20T08:01:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![swolfo](https://avatars.discourse-cdn.com/v4/letter/s/bb73d2/32.png) [@swolfo](https://discuss.elastic.co/u/swolfo)\
**Post date:** [April 20, 2018, 8:01am UTC](https://discuss.elastic.co/t/sql-to-es-query/128821/1 "2018-04-20T08:01:02Z")

</div>

Hi all,

I'm pretty new to Elastic, and I'm struggling in writing the right ES Query, corresponding to this simple SQL query:

`select * from TABLE where ID in (select ID from TABLE where 'level'='ERROR');`

Actually, what I really want to achieve would be something like:

`delete from TABLE where ID not in (select ID from TABLE where 'level'='ERROR');`

Any help would be much appreciated.

Thanks a lot,  
Cheers!

---

<div class="post-metadata">

**Author:** ![JKhondhu](https://avatars.discourse-cdn.com/v4/letter/j/ed655f/32.png) [@JKhondhu](https://discuss.elastic.co/u/JKhondhu)\
**Post date:** [April 20, 2018, 8:17am UTC](https://discuss.elastic.co/t/sql-to-es-query/128821/2 "2018-04-20T08:17:27Z")

</div>

What does the data look like held in Elasticsearch?

The immediate go to would be: [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-terms-query.html)

Example:

```auto
GET /whateverTableIndex/_search
{
    "query" : {
        "terms" : {
            "logging" : {
                "level" : "ERROR"
            }
        }
    }
}

```

Have a further look at that link above for the ID field also.

---

<div class="post-metadata">

**Author:** ![swolfo](https://avatars.discourse-cdn.com/v4/letter/s/bb73d2/32.png) [@swolfo](https://discuss.elastic.co/u/swolfo)\
**Post date:** [April 20, 2018, 8:30am UTC](https://discuss.elastic.co/t/sql-to-es-query/128821/3 "2018-04-20T08:30:09Z")

</div>

You're right, I'll try to be more specific:

my documents look like this:

```
{
  "_index": "logstash-2018.04.19",
  "_type": "logs",
  "_id": "AWLexDRMQFytjUcdloVk",
  "_version": 1,
  "_score": null,
  "_source": {
    "traceId": "42bjwa8hrmhma3jdasrmlmcre",
    "level": "ERROR",
    "idWorkOrderActivity": "693588",
    "env": "prf",
    "message": "WFLYEJB0034: EJB Invocation failed on component %s for method %s",
    "component": "acq",
    "hostname": "plaawiworj01",
    "@timestamp": "2018-04-19T16:37:03.231Z",
    "port": 37863,
    "thread_name": "default task-20",
    "level_value": 40000,
    "@version": 1,
    "host": "10.151.4.142",
    "logger_name": "org.jboss.as.ejb3.invocation",
    "stack_trace": "javax.ejb.EJBException ... etc"
}

```

What I need to do is:

extracting all the "idWorkOrderActivity" for which there is a document with a "level" = "ERROR" and then extract all the documents with those "idWorkOrderActivity".  
This would be the first SQL query I posted.

Final goal is to delete all the documents not matching the above criterias, hence the second SQL query.

Thank you very much,  
Simone

---

<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:** [May 18, 2018, 8:30am UTC](https://discuss.elastic.co/t/sql-to-es-query/128821/4 "2018-05-18T08:30:10Z")

</div>

Closed on May 18, 2018, 8:30am UTC

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