SQL to ES Query

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,

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


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

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

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": "",
    "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,

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