Elastic search return the most recent item per term

My index has a created_at date field, and another field account_id. How do I get the most recent document for each account?

I only found the term aggregate which aggregates per term, but it returns counts of items per term. Instead I need to sort and return the 1st result per term.

You need to use the term aggregation with the top hits sub aggregation to fetch the latest documents in each account_id.

term aggregation will create a bucket per account and later on you can sort all the documents in each bucket based on created_at and fetch just 1 document using top hits sub aggregation.

Example with data

Index sample data

{
  "date_field": "2022-10-31T00:00:00Z",
  "account_id" :1 
}

{
  "date_field": "2023-01-31T00:00:00Z",
  "account_id" :1 
}

{
  "date_field": "2023-01-31T00:00:00Z",
  "account_id" :2 
}

{
  "date_field": "2023-02-28T00:00:00Z",
  "account_id" :2 
}

{
  "date_field": "2023-03-09T00:00:00Z",
  "account_id" :4
}

Query to fetch the **latest document per account`

{
    "size": 0,
    "aggs": {
        "unique_account_ids": {
            "terms": {
                "field": "account_id"
            },
            "aggregations": {
                "latest_per_account": {
                    "top_hits": {
                        "from": 0,
                        "size": 1,
                        "sort": [
                            {
                                "date_field": {
                                    "order": "desc"
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
}

Result

 "buckets": [
                {
                    "key": 1,
                    "doc_count": 2,
                    "latest_per_account": {
                        "hits": {
                            "total": {
                                "value": 2,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "75680182",
                                    "_id": "2",
                                    "_score": null,
                                    "_source": {
                                        "date_field": "2023-01-31T00:00:00Z",
                                        "account_id": 1
                                    },
                                    "sort": [
                                        1675123200000
                                    ]
                                }
                            ]
                        }
                    }
                },
                {
                    "key": 2,
                    "doc_count": 2,
                    "latest_per_account": {
                        "hits": {
                            "total": {
                                "value": 2,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "75680182",
                                    "_id": "4",
                                    "_score": null,
                                    "_source": {
                                        "date_field": "2023-02-28T00:00:00Z",
                                        "account_id": 2
                                    },
                                    "sort": [
                                        1677542400000
                                    ]
                                }
                            ]
                        }
                    }
                },
                {
                    "key": 4,
                    "doc_count": 1,
                    "latest_per_account": {
                        "hits": {
                            "total": {
                                "value": 1,
                                "relation": "eq"
                            },
                            "max_score": null,
                            "hits": [
                                {
                                    "_index": "75680182",
                                    "_id": "5",
                                    "_score": null,
                                    "_source": {
                                        "date_field": "2023-03-09T00:00:00Z",
                                        "account_id": 4
                                    },
                                    "sort": [
                                        1678320000000
                                    ]
                                }
                            ]
                        }
                    }
                }
            ]