# What is the best way to create subquery with elasticsearch

**URL:** <https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623>\
**Category:** Elasticsearch\
**Created:** [May 7, 2020, 9:01pm UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623 "2020-05-07T21:01:02Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![scofield](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@scofield](https://discuss.elastic.co/u/scofield)\
**Post date:** [May 7, 2020, 9:01pm UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623/1 "2020-05-07T21:01:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![scofield](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@scofield](https://discuss.elastic.co/u/scofield)\
**Post date:** [May 12, 2020, 1:09am UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623/2 "2020-05-12T01:09:29Z")

</div>

Please, can anyone help build this query?

---

<div class="post-metadata">

**Author:** ![scofield](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@scofield](https://discuss.elastic.co/u/scofield)\
**Post date:** [May 13, 2020, 4:06pm UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623/3 "2020-05-13T16:06:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [May 13, 2020, 10:20pm UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623/4 "2020-05-13T22:20:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 14, 2020, 5:50am UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623/5 "2020-05-14T05:50:46Z")

</div>

Since you are only looking for conditions on the last record added you could perhaps [create a transform](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/put-transform.html) 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.

---

<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 11, 2020, 5:50am UTC](https://discuss.elastic.co/t/what-is-the-best-way-to-create-subquery-with-elasticsearch/231623/6 "2020-06-11T05:50:49Z")

</div>

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