# Complex bucket aggregation

**URL:** <https://discuss.elastic.co/t/complex-bucket-aggregation/281689>\
**Category:** Elasticsearch\
**Created:** [August 17, 2021, 1:44pm UTC](https://discuss.elastic.co/t/complex-bucket-aggregation/281689 "2021-08-17T13:44:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tkachleo](https://avatars.discourse-cdn.com/v4/letter/t/b38774/32.png) [@Tkachleo](https://discuss.elastic.co/u/Tkachleo)\
**Post date:** [August 17, 2021, 1:44pm UTC](https://discuss.elastic.co/t/complex-bucket-aggregation/281689/1 "2021-08-17T13:44:02Z")

</div>

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

```auto
{
  "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:

```auto
{
  "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!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 17, 2021, 4:36pm UTC](https://discuss.elastic.co/t/complex-bucket-aggregation/281689/2 "2021-08-17T16:36:31Z")

</div>

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](https://www.elastic.co/blog/how-to-use-transforms-to-track-your-most-recent-customer-orders)

---

<div class="post-metadata">

**Author:** ![Tkachleo](https://avatars.discourse-cdn.com/v4/letter/t/b38774/32.png) [@Tkachleo](https://discuss.elastic.co/u/Tkachleo)\
**Post date:** [August 18, 2021, 10:38am UTC](https://discuss.elastic.co/t/complex-bucket-aggregation/281689/3 "2021-08-18T10:38:57Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 18, 2021, 1:13pm UTC](https://discuss.elastic.co/t/complex-bucket-aggregation/281689/4 "2021-08-18T13:13:37Z")

</div>

> [@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](https://www.youtube.com/watch?v=yBf7oeJKH2Y). 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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 15, 2021, 1:13pm UTC](https://discuss.elastic.co/t/complex-bucket-aggregation/281689/5 "2021-09-15T13:13:47Z")

</div>

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