# Equivalent of 'Group By' along with significant\_text aggregation **URL:** \ **Category:** Elasticsearch\ **Created:** [September 11, 2021, 2:47pm UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944 "2021-09-11T14:47:52Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![firasd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/firasd/32/94447_2.png) [@firasd](https://discuss.elastic.co/u/firasd)\ **Post date:** [September 11, 2021, 2:47pm UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944/1 "2021-09-11T14:47:52Z")
Hi folks.. I'm looking for some equivalent of SQL's 'group by' or 'distinct' ... basically I have this query ```auto { "query": { "range": { "post_date": { "from": "2016-04-14 00:00:00", "to": "2016-04-15 00:00:00" } } }, "aggregations": { "keywords": { "significant_text": { "field": "post_content", "size": 50, "background_filter": { "range": { "post_date": { "from": "2016-01-01 00:00:00", "to": "2016-04-13 00:00:00" } } } } } } } ``` The problem is that if I have a lot of posts between April 14-April 15, 2016 in the same category it skews the results I'm looking for. For example if I entered 15 posts about "Jack" I don't actually don't want Jack to show up as `"doc_count": 15` if all entries are in the same category. The category is available as `term_id` in the documents like this: ```auto { "_index": "indexname", "_type": "_doc", "_id": "6149376", "_score": 1, "_source": { "post_id": 6149376, "ID": 6149376, "post_author": { "raw": "admin", "login": "admin", "display_name": "admin", "id": 1 }, "post_date": "2016-04-14 01:34:17", "post_date_gmt": "2016-04-14 01:34:17", "post_title": "title", "post_excerpt": "", "post_content_filtered": "content", "post_status": "publish", "post_name": "title", "post_modified": "2016-04-14 01:34:17", "post_modified_gmt": "2016-04-14 01:34:17", "post_parent": 0, "post_type": "post", "post_mime_type": "", "permalink": "https://example.com/title", "terms": { "category": [ { "term_id": 1, "slug": "uncategorized", "name": "Uncategorized", "parent": 0, "term_taxonomy_id": 1, "term_order": 0, "facet": "{\"term_id\":1,\"slug\":\"uncategorized\",\"name\":\"Uncategorized\",\"parent\":0,\"term_taxonomy_id\":1,\"term_order\":0}" } ], } } } ``` Would appreciate pointers on how to modify the query to aggregate by the `term_id`. If this can be done for the `background_filter` as well, it may also be helpful. ---
**Author:** ![firasd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/firasd/32/94447_2.png) [@firasd](https://discuss.elastic.co/u/firasd)\ **Post date:** [September 11, 2021, 5:26pm UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944/2 "2021-09-11T17:26:30Z")
Update, I found that the '`diversified_sampler`' can help, eg: ```auto { "size": 50, "query": { "range": { "post_date": { "from": "2016-05-01 00:00:00", "to": "2016-05-04 00:00:00" } } }, "aggregations": { "my_unbiased_sample": { "diversified_sampler": { "shard_size": 1000, "field": "terms.category.term_id" }, "aggregations": { "keywords": { "significant_text": { "field": "post_title", "size": 50, "background_filter": { "range": { "post_date": { "from": "2016-01-01 00:00:00", "to": "2016-05-30 00:00:00" } } } } } } } } } ``` But I realized something, this is picking just one doc per category (which is better than picking every doc in every category) but what I really want is to let each category only contribute one doc to a bucket for a particular keyword So rather than the equivalent of SQL's 'GROUP BY(category)' what I'm really after is something like 'GROUP BY(category, keyword)' if that's possible ---
**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:** [September 12, 2021, 5:56pm UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944/3 "2021-09-12T17:56:41Z")
> [@firasd](#): > > this is picking just one doc per category You can pick how many max docs per field value. Regardless, you may want to look at significant text’s filter\_duplicate\_text setting which can avoid skew in copy-pasted sections of text found in multiple docs. ---
**Author:** ![firasd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/firasd/32/94447_2.png) [@firasd](https://discuss.elastic.co/u/firasd)\ **Post date:** [September 13, 2021, 6:59am UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944/4 "2021-09-13T06:59:01Z")
Thanks, both those pointers (max docs per value and filter\_duplicate\_text) are helpful so will use them together I also found the "exclude" setting, so now I'm using it to exclude eg "18 06 2016" or "18 june" using regex like: ```auto "significant_text": { "field": "post_content", "exclude": "(.*)?2016(.*)?|(.*)?16(.*)?|(.*)?06(.*)?|(.*)?18(.*)?", "filter_duplicate_text": true, ``` ---
**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:** [September 13, 2021, 8:25am UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944/5 "2021-09-13T08:25:06Z")
Good to know. Hopefully you have all your content in one index/shard rather than many if possible. Most users have time based indices eg one per day and that is no use when trying to find stuff like trending topics on a day. The shard with today’s content has no history to compare it against and all the other shards don’t know what happened today so none of them can produce results. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 11, 2021, 8:25am UTC](https://discuss.elastic.co/t/equivalent-of-group-by-along-with-significant-text-aggregation/283944/6 "2021-10-11T08:25:48Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.