CODEWITHSUNDEEP
elasticsearch
BZelasky
BZelasky

Reputation: 3

elasticsearch - trying to find latest time stamp in aggregation

I have written a query in elastic search using range on a timestamp field. I need to find the latest timestamp for each group. First I perform the query, then group by the name field, then try to select the latest timestamp. But it is not working. Any suggestions are appreciated. Below is my json I use for the search Post query from postman

{ "query" : { "range" : { "timestamp" : { "gte" :"2016-10-05T15:00:35", "lte" : "2016-10-06T15:10:35" } } },

"aggs" : {
   "groupbyname" : { 
        "terms" : {
            "field" : "name"
            },  
            "aggs" : {
                "selectlatesttimestamp" :  {
                "max" : {
                    "field" : "timestamp"
                }
            }
        }

    }
}

}

Upvotes: 0

Views: 3987

Answers (1)

Or Weinberger
Or Weinberger

Reputation: 7472

You can use the top-hits aggregation to do that, as per your example, you could do something like this:

{
  "query": {
    "range": {
      "timestamp": {
        "gte": "2016-10-05T15:00:35",
        "lte": "2016-10-06T15:10:35"
      }
    }
  },
  "aggs": {
    "groupbyname": {
      "terms": {
        "field": "name"
      },
      "aggs": {
        "top_group_hits": {
          "top_hits": {
            "sort": [
              {
                "timestamp": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}

If you're not using the actual results originating from the query, but only use the aggregations, you can also add "size":0 to the top level object (above the "query").

If you only need some of the fields, you could include a _source section to your top hits aggregation, for example if you only need to get the latest timestamp you could do something like this:

{
  "size": 0,
  "query": {
    "range": {
      "timestamp": {
        "gte": "2016-10-05T15:00:35",
        "lte": "2016-10-06T15:10:35"
      }
    }
  },
  "aggs": {
    "groupbyname": {
      "terms": {
        "field": "name"
      },
      "aggs": {
        "top_group_hits": {
          "top_hits": {
            "sort": [
              {
                "timestamp": {
                  "order": "desc"
                }
              }
            ],
            "_source": [
              "timestamp"
            ],
            "size": 1
          }
        }
      }
    }
  }
}

Upvotes: 2

Related Questions