How to parse below SQL query to ES?

Elastic Stack Elasticsearch
1

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 ?

2

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

3

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 to update the document because timestamp field is an array type.

4

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"
          }
        }
      }
    }
  }
}
5

Please don't ping people like this.

Please read

Specifically the "be patient" part.

6

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

7

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!

8

Refer to 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!

9

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

10

Hi Can some one please help ?

12

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