# Group and keep only first result of that group **URL:** \ **Category:** Elasticsearch\ **Created:** [October 1, 2020, 8:53am UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624 "2020-10-01T08:53:38Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![lanwin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lanwin/32/76510_2.png) [@lanwin](https://discuss.elastic.co/u/lanwin)\ **Post date:** [October 1, 2020, 8:53am UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624/1 "2020-10-01T08:53:38Z")
Is there any way to do a search, do a group (and discard all but one result of that group)? We have a simple parent-\>children relationship (building also a group). Our problem is now that we have a complex filter and its often the case that the parent is not within the filter criteria but one or more of the children are. So we want to ensure we display one result per Group even if the parent is not within the filter (the top sorted child should be used then). My strongly simplefied dataset looks like this: ``` { id: "1", groupId: "a", groupSort: 1, date: "2016/01/02" }, { id: "2", groupId: "a", groupSort: 2, date: "2016/01/02" }, { id: "3", groupId: "b", groupSort: 1, date: "2016/01/01" }, { id: "4", groupId: "c", groupSort: 1, date: "2016/01/03" }, { id: "5", groupId: "c", groupSort: 2, date: "2016/01/02" } ``` What I want to get is this: ``` { id: "3", groupId: "b", groupSort: 1, date: "2016/01/01" }, { id: "1", groupId: "a", groupSort: 1, date: "2016/01/02" }, { id: "4", groupId: "c", groupSort: 1, date: "2016/01/03" } ``` What means: Group results by "groupId" with sort "groupSort" ASC and only keep the first result. Then sort the remaining items by "date". The resultset should be pageabe. Or is there any other way to solve our problem (one result per group)? PS: I thought about creating a document per group and then nest each member of that group within. But our documents are pretty huge and pretty complex its possible that a group can have 5000+ members. So I fear the performance if I do so. ---
**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:** [October 1, 2020, 9:26am UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624/2 "2020-10-01T09:26:44Z")
For grouping you need aggregations, e.g. `terms` on `groupId`, your tiebreaker could be `bucket_sort`, however: > [@lanwin](#): > > The resultset should be pageabe. For paging aggregation results you need a [composite aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html). Unfortunately `bucket_sort` is not supported in a composite aggregation, because it is a pipeline aggregation. But you don't need sort anyway, because you only want the top 1. This is doable with a painless script executed in a `scripted_metric` aggregation. We have a similar example in the [docs](https://www.elastic.co/guide/en/elasticsearch/reference/7.9/transform-painless-examples.html#painless-top-hits) The example brings me to the next suggestion, if you want to execute this query regularly, you should consider using [transforms](https://www.elastic.co/guide/en/elasticsearch/reference/7.9/transforms.html). With Transforms you can create a self-updating index that always contains the latest state of your grouping. Hope that helps! ---
**Author:** ![lanwin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lanwin/32/76510_2.png) [@lanwin](https://discuss.elastic.co/u/lanwin)\ **Post date:** [October 1, 2020, 1:24pm UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624/3 "2020-10-01T13:24:43Z")
Hi Hendrik thanks for your answer. I am not sure yet if they help. I want the full documents not, the groupId or the top date. ---
**Author:** ![lanwin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lanwin/32/76510_2.png) [@lanwin](https://discuss.elastic.co/u/lanwin)\ **Post date:** [October 1, 2020, 1:29pm UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624/4 "2020-10-01T13:29:10Z")
Here is my test dataset ```auto PUT test { "settings": { "number_of_shards": 1 }, "mappings": { "properties": { "id": { "type": "keyword" }, "groupId": { "type": "keyword" }, "groupSort": { "type": "integer" }, "date": { "type": "date" } } } } POST test/_doc/ { "id": "1", "groupId": "a", "groupSort": 1, "date": "2020-01-01T09:05:00.7318105" } POST test/_doc/ { "id": "2", "groupId": "a", "groupSort": 2, "date": "2020-01-02T09:05:00.7318105" } POST test/_doc/ { "id": "3", "groupId": "b", "groupSort": 1, "date": "2020-01-01T09:05:00.7318105" } POST test/_doc/ { "id": "4", "groupId": "c", "groupSort": 1, "date": "2020-01-03T09:05:00.7318105" } POST test/_doc/ { "id": "5", "groupId": "c", "groupSort": 2, "date": "2020-01-02T09:05:00.7318105" } ``` Btw: Our dataset has 30mio documents and a filter could span at max 2mio documents where we show the first 30 and allow to page the fist few pages. ---
**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:** [October 8, 2020, 11:18am UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624/5 "2020-10-08T11:18:44Z")
> [@lanwin](#): > > I want the full documents not, the groupId or the top date. The provided example from the docs returns full documents. The only missing piece is your additional sort criteria. E.g. this is what I get: ```auto { "key" : { "group_id" : "b" }, "doc_count" : 1, "latest_doc" : { "value" : { "date" : "2020-01-01T09:05:00.7318105", "id" : "3", "groupSort" : 1, "groupId" : "b" } } }, ``` The document is nested under `latest_doc.value`. Here is the full example query: ```auto GET test/_search { "size": 0, "aggs": { "c": { "composite": { "sources": [ { "group_id": { "terms": { "field": "groupId" } } } ] }, "aggs": { "latest_doc": { "scripted_metric": { "init_script": "state.timestamp_latest = 0L; state.last_doc = ''", "map_script": """ def current_date = doc['date'].getValue().toInstant().toEpochMilli(); if (current_date > state.timestamp_latest) {state.timestamp_latest = current_date; state.last_doc = new HashMap(params['_source']);} """, "combine_script": "return state", "reduce_script": """ def last_doc = ''; def timestamp_latest = 0L; for (s in states) {if (s.timestamp_latest > (timestamp_latest)) {timestamp_latest = s.timestamp_latest; last_doc = s.last_doc;}} return last_doc """ } } } } } } ``` With an additional member `best_sort_criteria` and some extra `if`, you can implement your additional criteria. > [@lanwin](#): > > Btw: Our dataset has 30mio documents and a filter could span at max 2mio documents where we show the first 30 and allow to page the fist few pages. Does not sound like a problem to me, I would start with the query. If you run into problems, e.g. because you run this query a lot (expensive despite caching) you can consider using transform. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 5, 2020, 11:18am UTC](https://discuss.elastic.co/t/group-and-keep-only-first-result-of-that-group/250624/6 "2020-11-05T11:18:50Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.