SQL to ES Query

[swolfo]

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!

[JKhondhu (Jymit Singh Khondhu)]

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

Example:

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

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

[swolfo]

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