Elastic how to get rows by distinct column which is ordered by another column?

Question

In my elastic index I have 3 fields userId, status, updatedAt with other fields. Now I am trying to get all rows where userId and status is distinct. I can do this with term aggregression.

But I have another requirement when multiple rows have the same userId and status i want to return the row with the smallest/earliest updatedAt field. That is while finding a distinct row, it should be ordered ascending by updatedAt.

How can i do this? Here is my attempt

GET user_history/_search
{
  "aggs": {
    "user": {
      "terms": {
        "field": "userId"
      },
      "aggs": {
        "status": {
          "terms": {
            "field": "info.status"
          }
        }
      }
    }
  }
}

Answer 1

You can use top_hits aggregation

{
  "aggs": {
    "user": {
      "terms": {
        "field": "userId"
      },
      "aggs": {
        "status": {
          "terms": {
            "field": "status.keyword"
          },
          "aggs": {
            "top_document": {
              "top_hits": {
                "size": 1,
                "sort": {"updatedAt":"desc"}
              }
            }
          }
        }
      }
    }
  }
}