Complex bucket aggregation

[Tkachleo]

Hi,
I need some help with a complex aggregation.
I have these 3 documents:

{
  "id": 1,
  "@timestamp": "2021-08-17 12:00:00",
  "result": "FAIL"
},
{
  "id": 2,
  "@timestamp": "2021-08-17 12:05:00",
  "result": "PASS"
},
{
  "id": 3,
  "@timestamp": "2021-08-17 12:07:00",
  "result": "FAIL"
},
{
  "id": 1,
  "@timestamp": "2021-08-17 12:10:00",
  "result": "PASS"
},

I need the result to look like 2 buckets:

{
  "result": "PASS",
  "doc_count": 2
},
{
  "result": "FAIL",
  "doc_count": 1
}

Meaning - I need to count the passes / fails of the latest instances of each id.

I tried achieving it using "top hits" agg, but I can't seem to finalize my solution.
Any ideas?
Thanks!

[Mark_Harwood]

How many unique IDs do you have in your system?
If there’s many you might want to create an entity centric index. See How to use transforms to track your most recent customer orders | Elastic Blog

[Tkachleo]

Yes, there are many unique IDs.
Is that the only way that this functionality can be achieved? Or is there an option to just have a complex aggregation?
I just want to understand if I have to create another index to solve this, or not.

Thanks!

[Mark_Harwood]

@Tkachleo

I just want to understand if I have to create another index to solve this, or not.

Typically, yes.
On the plus side there's all sorts of interesting attributes you can derive other than "last known state" when you do this. For some background on the problem see the first half of this presentation. The solution I propose is outdated now that we have the transforms API to do the hard work but the underlying problem it addresses with aggregations on raw data is still the same.