# Sub aggregating top\_hits **URL:** \ **Category:** Elasticsearch\ **Created:** [January 16, 2024, 12:19pm UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163 "2024-01-16T12:19:09Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Vivek\_Burman](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vivek_burman/32/118310_2.png) [@Vivek\_Burman](https://discuss.elastic.co/u/Vivek_Burman)\ **Post date:** [January 16, 2024, 12:19pm UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/1 "2024-01-16T12:19:09Z")
Hi, I've the below Query ```auto { "query": { "bool": { "filter": [ { "term": { "is_deleted": 0 } }, { "term": { "project.is_deleted": 0 } }, { "terms": { "project.pid": [ 1831 ] } } ] } }, "size": 0, "aggs": { "aq_agg_test_case": { "nested": { "path": "test_case" }, "aggs": { "aq_agg_name": { "terms": { "field": "test_case.pid" }, "aggs": { "aq_agg_top_hits": { "top_hits": { "sort": [ { "test_case.start_timestamp": "desc" } ], "size": 1 }, "aggs": { "aq_agg_reverse_2": { "reverse_nested": {}, "aggs": { "aq_agg_app_env": { "terms": { "field": "app_environment.name" } } } } } } } } } } } } ``` Getting error response: ```auto { "error": { "root_cause": [ { "type": "aggregation_initialization_exception", "reason": "Aggregator [aq_agg_top_hits] of type [top_hits] cannot accept sub-aggregations" } ], "type": "aggregation_initialization_exception", "reason": "Aggregator [aq_agg_top_hits] of type [top_hits] cannot accept sub-aggregations" }, "status": 500 } ``` The requirement is: Group the docs by ID's, then take the latest doc based on start\_timestamp[Date field] in each group. Further aggregate those single docs based on "app\_env" field. Can you help me understanding how take on this problem ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [January 16, 2024, 11:29pm UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/2 "2024-01-16T23:29:23Z")
Hi @Vivek_Burman Did you try like this: ```auto { "size": 0, "aggs": { "aq_agg_test_case": { "nested": { "path": "test_case" }, "aggs": { "aq_agg_name": { "terms": { "field": "test_case.pid" }, "aggs": { "aq_agg_top_hits": { "top_hits": { "sort": [ { "test_case.start_timestamp": "desc" } ], "size": 1 } }, "test_case_to_app_env": { "reverse_nested": {}, "aggs": { "aq_agg_app_env": { "terms": { "field": "app_environment.name" } } } } } } } } } } ``` ---
**Author:** ![Vivek\_Burman](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vivek_burman/32/118310_2.png) [@Vivek\_Burman](https://discuss.elastic.co/u/Vivek_Burman)\ **Post date:** [January 17, 2024, 8:48am UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/3 "2024-01-17T08:48:18Z")
I think this solution, maintains two parallel aggregations. One for top\_hits and other for app\_env. While I've a slight different requirement here which is collect all those first top\_hits docs and restart agg them based on app\_env. ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [January 17, 2024, 7:37pm UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/4 "2024-01-17T19:37:48Z")
For the exception we know that it is not possible to have a sub aggs after the top\_hits. I don't know, but maybe you add it first via app\_env. ---
**Author:** ![Vivek\_Burman](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vivek_burman/32/118310_2.png) [@Vivek\_Burman](https://discuss.elastic.co/u/Vivek_Burman)\ **Post date:** [January 19, 2024, 11:45am UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/5 "2024-01-19T11:45:51Z")
I tried pipeline aggregation as well but looks like the freedom is restricted on pipeline aggregation to re-aggregate the previous. But anyways I would love to know the reason behind this restriction in first place and hopefully it can be removed in future. We have couple of cases where we are only interested in the latest execution of jobs as we have couple of executions each day. ---
**Author:** ![Mark\_Harwood1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood1/32/101255_2.png) [@Mark\_Harwood1](https://discuss.elastic.co/u/Mark_Harwood1)\ **Post date:** [January 20, 2024, 8:52am UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/6 "2024-01-20T08:52:17Z")
Doing analytics on the last-known-state of an entity is a classic use case for the transform api. 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) ---
**Author:** ![Vivek\_Burman](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vivek_burman/32/118310_2.png) [@Vivek\_Burman](https://discuss.elastic.co/u/Vivek_Burman)\ **Post date:** [January 23, 2024, 7:51am UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/7 "2024-01-23T07:51:19Z")
Hi @Mark_Harwood1 thanks for sharing the article, I'm trying the pivot with painless script route, as it fits my needs. But have issue debugging it. Here is my query ```auto { "query": { "bool": { "filter": [ { "term": { "is_deleted": 0 } }, { "term": { "project.is_deleted": 0 } }, { "terms": { "project.pid": [ 1831 ] } } ] } }, "size": 0, "aggs": { "aq_agg_by_nested": { "nested": { "path": "test_case" }, "aggs": { "aq_agg_by_pid": { "terms": { "field": "test_case.pid" }, "aggregations": { "latest_doc": { "scripted_metric": { "init_script": "state.highest_pid=0;state.last_doc=null", "map_script": { "source": "if(doc['pid'].size() > 0 && doc['pid'].value>state.highest_pid){state.highest_pid=doc['pid'].value;state.last_doc=doc;}" }, "combine_script": { "source": "return state" }, "reduce_script": { "source": "return states" } } } } } } } } } ``` Two issues: First seems like doc['pid'].size() \> 0 is always false. Although there are results in the query. Second, I've a nested field named "test\_case" which is an array of objects I wish to iterate over properties inside the array tried using for(t in doc['test\_case'){...} getting error test\_case is field has no mapping, although /\_mapping api shows it as nested\_property Adding the response of above query here: ```auto { "took": 5, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 10000, "relation": "gte" }, "max_score": null, "hits": [] }, "aggregations": { "aq_agg_by_nested": { "doc_count": 454, "aq_agg_by_pid": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 68, "buckets": [ { "key": 12895, "doc_count": 126, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13417, "doc_count": 53, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13101, "doc_count": 50, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13102, "doc_count": 33, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13426, "doc_count": 25, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13394, "doc_count": 23, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13405, "doc_count": 23, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13395, "doc_count": 22, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13408, "doc_count": 16, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } }, { "key": 13164, "doc_count": 15, "latest_doc": { "value": [ { "highest_pid": 0, "last_doc": null } ] } } ] } } } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 20, 2024, 7:51am UTC](https://discuss.elastic.co/t/sub-aggregating-top-hits/351163/8 "2024-02-20T07:51:36Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.