Get latest record in group in ElasticSearch

Question

I have the following table structure

change_id    saveDate     id
-------------------------------
1            2014-04-01  205
2            2014-05-01  205
3            2014-03-31  111
4            2014-04-01  111
5            2014-04-01  333

Now I want to get the latest record for specific ids = [205, 111]. I should get the records with change_id [2,4].

My ElasticSearch query is so far:

    {
        "fields": [
            "Something.id",
            "Something.change_id",
            "Something.saveDate"
        ],
        "query": {
            "terms": {
                "Something.id": [
                    "2015",
                    "111"
                ]
            }
        },
        "sort": [
            {
                "Something.saveDate": {
                    "order": "desc"
                }
            }
        ],
        "aggs": {
            "id": {
                "terms": {
                    "field": "Something.id",
                    "size": 20
                }
            }
        }
    }

I am getting result of 4 rows change_id=[2, 1, 4, 3], which sorted desc by saveDate.

How can I filter that using query and get two rows where change_id=[4, 2] ?

Answer 1

You can use top_hits aggregation for this purpose. It also has source filtering and sorting capability.

{
  "query": {
    "bool": {
      "filter": {
        "terms": {
          "Something.id": [
            "205",
            "111"
          ]
        }
      }
    }
  },
  "size": 0,
  "aggs": {
    "unique_id": {
      "terms": {
        "field": "Something.id",
        "size": 20
      },
      "aggs": {
        "top_result": {
          "top_hits": {
            "size": 1,
            "_source": ["Something.id", "Something.change_id", "Something.saveDate"],
            "sort": {
              "Something.saveDate" : "desc"
            }
          }
        }
      }
    }
  }
}