Get latest record by ID in ElasticSearch

Question

I have an index with thousands of documents of the following type:

{
  "user_id" : 1234,
  "acc_id" : 4321,
  "type_of_event" : "event",
  "event_label" : "example activity",
  "time_stamp" : 1582720371,
  "event_info" : "example info",
  "time_stamp_string" : "2022-02-26 12:32:51"
}

I want a query that returns the last record (by time_stamp_string) for each user_id. What I tried was:

GET my_index/_search
{
  "aggs": {
    "top_id": {
      "max": {
        "field": "user_id"
      }
    }
  }
}

Which failed, returning entries older than the newest one. I can't see why this wouldn't work.

Answer 1

it doesn't do what you want because you are not factoring in the timestamp in anyway, you are only asking Elasticsearch to show you the largest value of user_id

the best way is to use a top hits agg along with a terms agg, something like this;

POST /my_index/_search
{
  "aggs": {
    "user_id": {
      "terms": {
        "field": "user_id"
      },
      "aggs": {
        "latest_time_stamp": {
          "top_hits": {
            "sort": [
              {
                "time_stamp": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}

what that does is identify each unique user_id and then find the latest value of the time_stamp for each one of those