# Filter buckets after top\_hits aggregation **URL:** \ **Category:** Elasticsearch\ **Created:** [May 11, 2020, 2:55am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913 "2020-05-11T02:55:42Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![ajonat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajonat/32/68042_2.png) [@ajonat](https://discuss.elastic.co/u/ajonat)\ **Post date:** [May 11, 2020, 2:55am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/1 "2020-05-11T02:55:42Z")
Hi, I have an index with documents that only have 3 fields: id, timestamp and status. I want to retrieve the newest document for each id, but: if that document's status is equals to "something", then I would like to ignore that bucket completely. What's the best way to do this? I was able to retrieve the newest document for each id with the query below but I don't know how to filter buckets based on the document's status. ```auto { "size": 0, "query": { "match_all": {} }, "aggs": { "group_by_order_id": { "terms": { "field": "id.keyword" }, "aggs": { "top_group_hits": { "top_hits": { "sort": [ { "timestamp": { "order": "desc" } } ], "size": 1 } } } } } } ``` Thanks, Alex. ---
**Author:** ![ajonat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajonat/32/68042_2.png) [@ajonat](https://discuss.elastic.co/u/ajonat)\ **Post date:** [May 12, 2020, 7:04pm UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/2 "2020-05-12T19:04:33Z")
Bump ---
**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:** [May 13, 2020, 3:25pm UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/3 "2020-05-13T15:25:32Z")
> [@ajonat](#): > > I want to retrieve the newest document for each id, but: if that document's status is equals to "something", then I would like to ignore that bucket completely. > > What's the best way to do this? Use a query other than "match\_all". You can use a `bool` query with a `must_not` expression and put a "match" expression ion there for the status you want to ignore. ---
**Author:** ![ajonat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajonat/32/68042_2.png) [@ajonat](https://discuss.elastic.co/u/ajonat)\ **Post date:** [May 14, 2020, 12:10am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/4 "2020-05-14T00:10:49Z")
Hi Mark, I don't think that is going to work. Suppose I have 2 events: id: 1, status: A, timestamp: 2020-05-05 id: 1, status: B, timestamp: 2020-04-04 If I filter out status A in the query, I'd get a bucket with the second event (status B). What I really want is to filter out the bucket if the newest document (in this example, the first event) contains status A. So, the result of my query would be 0 buckets or a single empty bucket (with no hits). ---
**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:** [May 14, 2020, 7:32am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/5 "2020-05-14T07:32:09Z")
Ah gotcha. Sounds like a “last known status” problem for which an entity centric index is best. For that see the transform api. ---
**Author:** ![ajonat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajonat/32/68042_2.png) [@ajonat](https://discuss.elastic.co/u/ajonat)\ **Post date:** [May 14, 2020, 7:56pm UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/6 "2020-05-14T19:56:08Z")
Thank you for the tip Mark 🙂! Will look into that API. ---
**Author:** ![CSharpBender](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/csharpbender/32/50377_2.png) [@CSharpBender](https://discuss.elastic.co/u/CSharpBender)\ **Post date:** [May 14, 2020, 8:23pm UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/7 "2020-05-14T20:23:32Z")
Try with bucket selector [https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-aggregations-pipeline-bucket-selector-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-aggregations-pipeline-bucket-selector-aggregation.html) ---
**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:** [May 15, 2020, 7:36am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/8 "2020-05-15T07:36:57Z")
One of the issues with trimming via bucket selectors is that after trimming you may find you have no buckets left at all and have to go back and ask for more data with more searches. It can be a workable solution but depends on the data and the worst case scenario is very inefficient. ---
**Author:** ![ajonat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajonat/32/68042_2.png) [@ajonat](https://discuss.elastic.co/u/ajonat)\ **Post date:** [May 24, 2020, 12:35am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/9 "2020-05-24T00:35:05Z")
Hi Mark, I tried to use the transform API but it doesn't seem to support top\_hits aggregation. [https://www.elastic.co/guide/en/elasticsearch/reference/current/put-transform.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/put-transform.html) How would you do it? ---
**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\ **Post date:** [May 25, 2020, 5:51am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/10 "2020-05-25T05:51:38Z")
Hi, please have a look at this [painless example](https://www.elastic.co/guide/en/elasticsearch/reference/7.7/transform-painless-examples.html#painless-top-hits) (This should also work in older versions). For filtering out complete buckets/documents I suggest to use a [drop processor](https://www.elastic.co/guide/en/elasticsearch/reference/current/drop-processor.html) that runs _after_ the pivot. This can be done with an ingest pipeline, which you can specify as part of the transform destination. ---
**Author:** ![ajonat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajonat/32/68042_2.png) [@ajonat](https://discuss.elastic.co/u/ajonat)\ **Post date:** [May 25, 2020, 6:30am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/11 "2020-05-25T06:30:26Z")
Hi Hendrik, I followed the painless example and I was able to transform the index into a "last document" index grouped by id. As my source index is prefixed by the date the doc was put (sample indexes: docs-2020-05-25, docs-2020-04-24, ...), can I do the same for the index generated by the transform API? I would like to generate indexes like: latest-doc-by-id-2020-05-25, latest-doc-by-id-2020-05-24, ... Thanks, Alex ---
**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\ **Post date:** [May 25, 2020, 9:12am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/12 "2020-05-25T09:12:17Z")
Great that you found a solution, regarding your follow up question: I am thinking of ingest again and using the set processor to set `_index`: [https://www.elastic.co/guide/en/elasticsearch/reference/current/accessing-data-in-pipelines.html#accessing-metadata-fields](https://www.elastic.co/guide/en/elasticsearch/reference/current/accessing-data-in-pipelines.html#accessing-metadata-fields). The name for this index could be based on a field you create with transform, e.g. as part of the script. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 22, 2020, 9:13am UTC](https://discuss.elastic.co/t/filter-buckets-after-top-hits-aggregation/231913/13 "2020-06-22T09:13:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.