Elasticsearch top_hits aggregation vs latest document

Question

I am trying to get a list of users who has as their last activity "connect". Ideally, I want this as a metric viz or a data table in Kibana showing the number of users that connected last and the list of them, respectively. I have, however, given up being able to do this in Kibana. I can get something similar directly from Elasticsearch using a terms aggregation followed by top_hits as below. But the problem is, even though I am sorting the top_hits by @timestamp, the resulting document in NOT the most recent.

{
"size" : 0,
"sort": { "@timestamp": {"order": "desc"} },
"aggs" : {
    "by_user" : {
    "terms" : { 
            "field" : "fields.username.keyword",
            "size" : 1
        },
        "aggs": {
            "last_message": {
                "top_hits": {
                    "sort": [
                        {
                            "@timestamp": {
                                "order": "desc"
                            }
                        }
                    ],
                    "_source": {
                        "includes": ["fields.username.keyword", "@timestamp", "status"]
                    },
                    "size": 1
                }
            }
        }
        }
}
}

Is there a way to do this directly in Kibana?
How can I make sure top_hits gives me the latest results, rather than the "most relevant"?

Do you want a list of the last connect events (and the same user could appear multiple times in that list) or should there be only one event per user (the very last one)? — xeraa, Feb 16 '19 at 23:43
I only need the very last event, hence the size:1 in the top_hits aggregation. The idea is to list the users whos last event was a connect. — Acalypha, Feb 17 '19 at 14:06

score 1 · Accepted Answer · answered Feb 17 '19 at 23:16

I think what you want is field collapsing, which is faster than an aggregation.

Something like this should work for your use case:

GET my-index/_search {
    "query": {
        "match_all": { }
    },
    "collapse" : {
        "field" : "fields.username.keyword" 
    },
    "sort": [ {
        "@timestamp": {
            "order": "desc"
         }
    } ] }

I might be missing something, but I don't think Kibana supports this at the moment.

Elasticsearch top_hits aggregation vs latest document

1 Answers1