# How to parse below SQL query to ES?

**URL:** <https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734>\
**Category:** Elasticsearch\
**Created:** [October 4, 2017, 4:11pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734 "2017-10-04T16:11:21Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![kedarsdixit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kedarsdixit/32/20502_2.png) [@kedarsdixit](https://discuss.elastic.co/u/kedarsdixit)\
**Post date:** [October 4, 2017, 4:11pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/1 "2017-10-04T16:11:21Z")

</div>

I have documents such as:

{  
{id:1, timestamp:123456897} ,  
{id:1, timestamp:123456893},  
{id:1, timestamp:123356897}  
}

Suppose I need to get highest time stamp for each id ? (assuming there can be multiple ids and each id has multiple timestamps.)

SQL query can be:

SELECT id, max(timestamp) FROM table GROUP BY id;

Can some one please help ?

---

<div class="post-metadata">

**Author:** ![kedarsdixit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kedarsdixit/32/20502_2.png) [@kedarsdixit](https://discuss.elastic.co/u/kedarsdixit)\
**Post date:** [October 4, 2017, 4:14pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/2 "2017-10-04T16:14:07Z")

</div>

@dadoonet - Could you please help ? Many Thanks, ~Kedar

---

<div class="post-metadata">

**Author:** ![rockybean](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rockybean/32/11443_2.png) [@rockybean](https://discuss.elastic.co/u/rockybean)\
**Post date:** [October 4, 2017, 4:21pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/3 "2017-10-04T16:21:46Z")

</div>

Just do a simple test and it seems that this can solve your problem. Give it a try!

```
PUT test_max/data/1
{
  "id":1,
  "timestamp":[10,23,23]
}

PUT test_max/data/2
{
  "id":1,
  "timestamp":[10,232,23]
}

GET test_max/data/_search
{
  
  "size":1,
  "aggs": {
    "a": {
      "terms": {
        "field": "id",
        "size": 10
      },
      "aggs": {
        "max": {
          "max": {
            "field": "timestamp"
          }
        }
      }
    }
  }
}

```

By the way, you should use [update by script](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-update.html#_scripted_updates) to update the document because `timestamp` field is an array type.

---

<div class="post-metadata">

**Author:** ![rockybean](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rockybean/32/11443_2.png) [@rockybean](https://discuss.elastic.co/u/rockybean)\
**Post date:** [October 4, 2017, 4:24pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/4 "2017-10-04T16:24:58Z")

</div>

If your document is like below, just use the query I give above.

```
{id:1, timestamp:123456897} ,
{id:1, timestamp:123456893},
{id:1, timestamp:123356897}

```

The query is like below.

```
GET test_max/data/_search
{
  
  "size":1,
  "aggs": {
    "a": {
      "terms": {
        "field": "id",
        "size": 10
      },
      "aggs": {
        "max": {
          "max": {
            "field": "timestamp"
          }
        }
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 4, 2017, 6:35pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/5 "2017-10-04T18:35:23Z")

</div>

Please don't ping people like this.

Please read

> [@About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21):
>
> The heart of the free and open Elastic Stack Elasticsearch is a distributed, RESTful search and analytics engine capable of addressing a growing number of use cases. As the heart of the Elastic Stack, it centrally stores your data for lightning fast search, fine‑tuned relevancy, and powerful analytics that scale with ease. warning PLEASE READ THIS SECTION IF IT'S YOUR FIRST POST Some useful links: [elasticsearch reference guide](http://www.elastic.co/guide/en/elasticsearch/reference/current/index.html)[elasticsearch user guide](http://www.elastic.co/guide/en/elasticsearch/guide/current/index.html)[elasticsearch plugins](https://www.elastic.co/guide/en/elasticsearch/plugins/current/index.html)[elasticsearch cl…](https://www.elastic.co/guide/en/elasticsearch/client/index.html)

Specifically the "be patient" part.

---

<div class="post-metadata">

**Author:** ![kedarsdixit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kedarsdixit/32/20502_2.png) [@kedarsdixit](https://discuss.elastic.co/u/kedarsdixit)\
**Post date:** [October 4, 2017, 7:53pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/6 "2017-10-04T19:53:30Z")

</div>

I am sorry @dadoonet. I will take care going forward!

---

<div class="post-metadata">

**Author:** ![kedarsdixit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kedarsdixit/32/20502_2.png) [@kedarsdixit](https://discuss.elastic.co/u/kedarsdixit)\
**Post date:** [October 4, 2017, 7:55pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/7 "2017-10-04T19:55:03Z")

</div>

thanks @rockybean, I am using java APIs to pull the data, could you please help me in making use of them for above queries ? Thanks!

---

<div class="post-metadata">

**Author:** ![rockybean](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rockybean/32/11443_2.png) [@rockybean](https://discuss.elastic.co/u/rockybean)\
**Post date:** [October 4, 2017, 11:44pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/8 "2017-10-04T23:44:42Z")

</div>

Refer to [https://www.elastic.co/guide/en/elasticsearch/client/java-api/current/\_bucket\_aggregations.html#java-aggs-bucket-terms](https://www.elastic.co/guide/en/elasticsearch/client/java-api/current/_bucket_aggregations.html#java-aggs-bucket-terms) . It should not be too complicated for you. Give it a shot!

---

<div class="post-metadata">

**Author:** ![kedarsdixit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kedarsdixit/32/20502_2.png) [@kedarsdixit](https://discuss.elastic.co/u/kedarsdixit)\
**Post date:** [October 5, 2017, 10:18am UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/9 "2017-10-05T10:18:24Z")

</div>

Thanks, It is not helpful actually. Can you please help me with better option ?

---

<div class="post-metadata">

**Author:** ![kedarsdixit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kedarsdixit/32/20502_2.png) [@kedarsdixit](https://discuss.elastic.co/u/kedarsdixit)\
**Post date:** [October 6, 2017, 1:25pm UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/10 "2017-10-06T13:25:23Z")

</div>

Hi Can some one please help ?

---

<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:** [November 6, 2017, 6:32am UTC](https://discuss.elastic.co/t/how-to-parse-below-sql-query-to-es/102734/12 "2017-11-06T06:32:10Z")

</div>

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