Filter after aggregation

Elastic Stack Elasticsearch
1

I have documents in the form of:

{
"result": "failure",
"key": "A"
}
{
"result": "failure",
"key": "A"
}
{
"result": "failure",
"key": "A"
}
{
"result": "failure",
"key": "B"
}

I need to get the value of the 'key' field if the latest 3 documents have the result field set to 'failure'.
In the example above I would get the 'A' but not 'B'.

I tried terms aggregation by key and get the 3 latest documents with top_hits. But I cannot add anything to filter the results I get from these aggregations because it does not seem to be possible to nest any aggregations inside of a top_hit aggregation. I also tried a scripted_field that is 1 or 0 depending on the result field. But I also cannot build a sum depending on the scripted_field value.

{
"aggs": {
"termsByKey": {
"terms": {
"field": "key.keyword"
},
"aggs": {
"topHitsByKey": {
"top_hits": {
"_source": {
"includes": [
"key",
"result",
"@timestamp"
]
},
"size": 3
}
}
}
}
}
}

I do not know if this is actually the right approach to solve my initial problem.

2

Hey,

limiting the number of documents to three is a good start, but you should also sort by the timestamp of those documents to really get the latest ones.

Can you explain why a terms aggregation is not enough, as you would know if the last three documents either contain only A or B as well, by counting the number of buckets or just using the cardinality aggregation within the terms agg, by counting the number of distinct elements.

Can you explain why you need the originial JSON via top_hits? Maybe that request can be shortened a bit...

3

I do not need top_hits but it was the only way I could think of to reduce the number of documents to three. After applying my aggregation I get one bucket per key value that contains three elements. The problem now is that I have to check if those three elements have the desired value in the result field. Therefor the terms aggregation on the key field is not enough.

I cannot add any additional aggregation to work on the output of my topHitsByKey aggregation.

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.