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.

CodePudding user response：

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