How to parse below SQL query to ES?


(Kedar Dixit) #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 ?


(Kedar Dixit) #2

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


(Rockybean) #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.


(Rockybean) #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"
          }
        }
      }
    }
  }
}

(David Pilato) #5

Please don't ping people like this.

Please read

Specifically the "be patient" part.


(Kedar Dixit) #6

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


(Kedar Dixit) #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!


Convert SQL query to Java Elastic Query
(Rockybean) #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!


(Kedar Dixit) #9

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


(Kedar Dixit) #10

Hi Can some one please help ?


(system) #12

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