What is the best way to create subquery with elasticsearch

[scofield (M Scofield)]

I have an alert that is trigged every time the last record added has the field rabbitmq.queue.name == 'service_test_error' and the field rabbitmq.queue.messages.total.count != '0'

The sql query below works for my search, but I could not to do the same query with elasticsearch

select * from metric where rabbitmq.queue.messages.total.count != '0' and timestamp = (select max(timestamp) from metric where rabbitmq.queue.name = 'service_test_error'

These records below are examples that exist in my metric-xpto index

[
  {
    "_index": "metric-xpto",
    "_type": "_doc",
    "_id": "jYP1WnEBmYyEo7K68Zme",
    "_version": 1,
    "_score": null,
    "_source": {
      "@timestamp": "2020-04-08T18:03:14.899Z",
      "rabbitmq": {
        "queue": {
          "name": "service_test_error",
          "messages": {
            "total": {
              "count": 0
            }
          }
        }
      }
    }
  },
  {
    "_index": "metric-xpto",
    "_type": "_doc",
    "_id": "jYP1WnEBmYyEo7K68Zme",
    "_version": 1,
    "_score": null,
    "_source": {
      "@timestamp": "2020-04-07T18:03:14.899Z",
      "rabbitmq": {
        "queue": {
          "name": "service_test_error",
          "messages": {
            "total": {
              "count": 3
            }
          }
        }
      }
    }
  },
  {
    "_index": "metric-xpto",
    "_type": "_doc",
    "_id": "jYP1WnEBmYyEo7K68Zme",
    "_version": 1,
    "_score": null,
    "_source": {
      "@timestamp": "2020-04-03T17:03:14.899Z",
      "rabbitmq": {
        "queue": {
          "name": "service_alpha_test_error",
          "messages": {
            "total": {
              "count": 8
            }
          }
        }
      }
    }
  },
  {
    "_index": "metric-xpto",
    "_type": "_doc",
    "_id": "jYP1WnEBmYyEo7K68Zme",
    "_version": 1,
    "_score": null,
    "_source": {
      "@timestamp": "2020-04-03T18:03:14.899Z",
      "rabbitmq": {
        "queue": {
          "name": "service_test_error",
          "messages": {
            "total": {
              "count": 8
            }
          }
        }
      }
    }
  }
]

How can I create a similar query using elasticseacrh?

[scofield (M Scofield)]

Please, can anyone help build this query?

[scofield (M Scofield)]

My aim is to get the lastest record inserted with rabbitmq.queue.name = 'service_test_error' (whether ordered by timestamp desc or getting max timestamp) but in a second moment I need to check if this matched doc contain rabbitmq.queue.messages.total.count != '0'.
Given these conditions, the final result of the query needs to tell me whether there is a return or not.

[warkolm (Mark Walkom)]

There's no such thing as a sub-query in Elasticsearch, so you will need to add that count check in with the other conditions.

[Christian_Dahlqvist]

Since you are only looking for conditions on the last record added you could perhaps create a transform that maintains a separate index which contains only the latest document inserted. If you are monitoring multiple serices (or some other type of entity) it would create a document per entity. This makes it very easy and efficient to instead write a query against this index as you know you are always looking at the latest entry.

If you do not want to use transforms you can also simply write the document to a separate index and use the entity you are tracking as ID. As new documents come in they will overwrite the previous ones and you have an index containing only the latest version.