# GROUP BY query in ElasticSearch **URL:** https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483 **Category:** Elasticsearch **Created:** [March 20, 2014, 2:55am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483 "2014-03-20T02:55:57Z") **Posts on this page:** 11 **Page:** 1
### Author: ![tiennm](https://avatars.discourse-cdn.com/v4/letter/t/cab0a1/32.png) [@tiennm](https://discuss.elastic.co/u/tiennm) #### Post date: [March 20, 2014, 2:55am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/1 "2014-03-20T02:55:57Z")
Hello, In SQL i can use GROUP BY to limit number of rows in each group like bellow. Is there any way to do that in ElasticSearch? Thanks. 1. _Find the first 10 groups of cars, with 2 hits in each group:_ SELECT category, city, makemodel FROM cars GROUP BY category TOP 2 _Results:_ # ======================================================================================== | category | city | makemodel | # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | 1. _Find the numbers of cars in different categories:_ SELECT category, grouphitscount FROM cars GROUP BY category top 1 _Results:_ +---------------+----------------+ | category | grouphitscount | +---------------+----------------+ | compact | 4169 | | exotic | 1902 | -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet) #### Post date: [March 20, 2014, 6:53am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/2 "2014-03-20T06:53:56Z")
Have a look at aggregations. -- David 馃槈 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.nguyenmanh@gmail.com](mailto:tien.nguyenmanh@gmail.com) a 茅crit : Hello, In SQL i can use GROUP BY to limit number of rows in each group like bellow. Is there any way to do that in ElasticSearch? Thanks. Find the first 10 groups of cars, with 2 hits in each group: SELECT category, city, makemodel FROM cars GROUP BY category TOP 2 Results: # ======================================================================================== | category | city | makemodel | # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | Find the numbers of cars in different categories: SELECT category, grouphitscount FROM cars GROUP BY category top 1 Results: +---------------+----------------+ | category | grouphitscount | +---------------+----------------+ | compact | 4169 | | exotic | 1902 | -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![tiennm](https://avatars.discourse-cdn.com/v4/letter/t/cab0a1/32.png) [@tiennm](https://discuss.elastic.co/u/tiennm) #### Post date: [March 20, 2014, 9:23am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/3 "2014-03-20T09:23:58Z")
Thanks David, If you check my sample above, i don't want any aggregate info (min, max, count, ...) for each group, just want to get top N result from each group, is this possible? On Thu, Mar 20, 2014 at 1:53 PM, David Pilato [david@pilato.fr](mailto:david@pilato.fr) wrote: > Have a look at aggregations. > > -- > David 馃槈 > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.nguyenmanh@gmail.com](mailto:tien.nguyenmanh@gmail.com) a > 茅crit : > > Hello, > > In SQL i can use GROUP BY to limit number of rows in each group like > bellow. > Is there any way to do that in Elasticsearch? > > Thanks. > > 1. > > _Find the first 10 groups of cars, with 2 hits in each group:_ > > SELECT category, city, makemodel > FROM cars > GROUP BY category TOP 2 > > _Results:_ > > # ======================================================================================== | category | city | makemodel | > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > 1. > > _Find the numbers of cars in different categories:_ > > SELECT category, grouphitscount > FROM cars > GROUP BY category top 1 > > _Results:_ > > +---------------+----------------+ > | category | grouphitscount | > +---------------+----------------+ > | compact | 4169 | > | exotic | 1902 | > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr)[https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet) #### Post date: [March 20, 2014, 9:36am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/4 "2014-03-20T09:36:22Z")
Terms aggregation should be what you are looking for. -- David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) @dadoonet | @elasticsearchfr Le 20 mars 2014 脿 10:24:02, Nguyen Manh Tien ([tien.nguyenmanh@gmail.com](mailto:tien.nguyenmanh@gmail.com)) a 茅crit: Thanks David, If you check my sample above, i don't want any aggregate info (min, max, count, ...) for each group, just want to get top N result from each group, is this possible? On Thu, Mar 20, 2014 at 1:53 PM, David Pilato [david@pilato.fr](mailto:david@pilato.fr) wrote: Have a look at aggregations. -- David 馃槈 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.nguyenmanh@gmail.com](mailto:tien.nguyenmanh@gmail.com) a 茅crit : Hello, In SQL i can use GROUP BY to limit number of rows in each group like bellow. Is there any way to do that in ElasticSearch? Thanks. Find the first 10 groups of cars, with 2 hits in each group: SELECT category, city, makemodel FROM cars GROUP BY category TOP 2 Results: # ======================================================================================== | category | city | makemodel | # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | Find the numbers of cars in different categories: SELECT category, grouphitscount FROM cars GROUP BY category top 1 Results: +---------------+----------------+ | category | grouphitscount | +---------------+----------------+ | compact | 4169 | | exotic | 1902 | -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). ## To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). To unsubscribe from this group and all its topics, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/etPan.532ab696.3fa62aca.97ca%40MacBook-Air-de-David.local](https://groups.google.com/d/msgid/elasticsearch/etPan.532ab696.3fa62aca.97ca%40MacBook-Air-de-David.local). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![tiennm](https://avatars.discourse-cdn.com/v4/letter/t/cab0a1/32.png) [@tiennm](https://discuss.elastic.co/u/tiennm) #### Post date: [March 20, 2014, 9:48am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/5 "2014-03-20T09:48:30Z")
Check terms aggregation, it allow return specified top field in a group, can i return the whole doc there? On Thu, Mar 20, 2014 at 4:36 PM, David Pilato [david@pilato.fr](mailto:david@pilato.fr) wrote: > Terms aggregation should be what you are looking for. > > -- > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr[https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > Le 20 mars 2014 脿 10:24:02, Nguyen Manh Tien ([tien.nguyenmanh@gmail.com](mailto:tien.nguyenmanh@gmail.com)) > a 茅crit: > > Thanks David, > > If you check my sample above, i don't want any aggregate info (min, max, > count, ...) for each group, just want to get top N result from each group, > is this possible? > > On Thu, Mar 20, 2014 at 1:53 PM, David Pilato [david@pilato.fr](mailto:david@pilato.fr) wrote: > > > Have a look at aggregations. > > > > -- > > David 馃槈 > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.nguyenmanh@gmail.com](mailto:tien.nguyenmanh@gmail.com) a > > 茅crit : > > > > Hello, > > > > In SQL i can use GROUP BY to limit number of rows in each group like > > bellow. > > Is there any way to do that in Elasticsearch? > > > > Thanks. > > > > 1. > > > > _Find the first 10 groups of cars, with 2 hits in each group:_ > > > > SELECT category, city, makemodel > > FROM cars > > GROUP BY category TOP 2 > > > > _Results:_ > > > > # ======================================================================================== | category | city | makemodel | > > > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > 1. > > > > _Find the numbers of cars in different categories:_ > > > > SELECT category, grouphitscount > > FROM cars > > GROUP BY category top 1 > > > > _Results:_ > > > > +---------------+----------------+ > > | category | grouphitscount | > > +---------------+----------------+ > > | compact | 4169 | > > | exotic | 1902 | > > > > -- > > You received this message because you are subscribed to the Google > > Groups "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > ## To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm_medium=email&utm_source=footer) . For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > You received this message because you are subscribed to a topic in the > > Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit > > [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr)[https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm_medium=email&utm_source=footer). > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/etPan.532ab696.3fa62aca.97ca%40MacBook-Air-de-David.local](https://groups.google.com/d/msgid/elasticsearch/etPan.532ab696.3fa62aca.97ca%40MacBook-Air-de-David.local)[https://groups.google.com/d/msgid/elasticsearch/etPan.532ab696.3fa62aca.97ca%40MacBook-Air-de-David.local?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/etPan.532ab696.3fa62aca.97ca%40MacBook-Air-de-David.local?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CANihJfXAHRwDP9T1%2BTsUR24CGqv3MQVBnFgTD9Q6imApG\_R37Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfXAHRwDP9T1%2BTsUR24CGqv3MQVBnFgTD9Q6imApG_R37Q%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![otisg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/otisg/32/492_2.png) [@otisg](https://discuss.elastic.co/u/otisg) #### Post date: [March 25, 2014, 2:20am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/6 "2014-03-25T02:20:42Z")
Hi David, I looked around and haven't been able to find any examples of bucket aggregations other than terms one. I think the way this aggregation is described makes it sound like group by in RDBMS world, but it looks like that's not the case. Take this pseudo-example: SELECT category, city, makemodel FROM cars GROUP BY category TOP 2 In a DB world this could return the following: # ======================================================================================== | category | city | makemodel | # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | Note how each row is a full row/document. I think this is the key part. Can ES aggregations do this? If so, could you please share an example or point me to it? None of the examples of bucket aggregates I saw showed this. As far as I can tell, the above cannot be done with ES aggregations because it's essentially like "field collapsing" functionality that's not in ES yet, but maybe I'm missing something... ## Thanks, Otis Performance Monitoring \* Log Analytics \* Search Analytics Solr & Elasticsearch Support \* [http://sematext.com/](http://sematext.com/) On Thursday, March 20, 2014 5:36:22 AM UTC-4, David Pilato wrote: > Terms aggregation should be what you are looking for. > > -- > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr[https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > Le 20 mars 2014 脿 10:24:02, Nguyen Manh Tien ([tien.ng...@gmail.com](mailto:tien.ng...@gmail.com)\) > a 茅crit: > > Thanks David, > > If you check my sample above, i don't want any aggregate info (min, max, > count, ...) for each group, just want to get top N result from each group, > is this possible? > > On Thu, Mar 20, 2014 at 1:53 PM, David Pilato \<[da...@pilato.fr](mailto:da...@pilato.fr)\ > > > wrote: > > > Have a look at aggregations. > > > > -- > > David 馃槈 > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > Le 20 mars 2014 脿 03:55, Nguyen Manh Tien \<[tien.ng...@gmail.com](mailto:tien.ng...@gmail.com)\\> > > a 茅crit : > > > > Hello, > > > > In SQL i can use GROUP BY to limit number of rows in each group like > > bellow. > > Is there any way to do that in Elasticsearch? > > > > Thanks. > > > > 1. > > > > _Find the first 10 groups of cars, with 2 hits in each group:_ > > > > SELECT category, city, makemodel > > FROM cars > > GROUP BY category TOP 2 > > > > _Results:_ > > > > # ======================================================================================== | category | city | makemodel | > > > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > 1. > > > > _Find the numbers of cars in different categories:_ > > > > SELECT category, grouphitscount > > FROM cars > > GROUP BY category top 1 > > > > _Results:_ > > > > +---------------+----------------+ > > | category | grouphitscount | > > +---------------+----------------+ > > | compact | 4169 | > > | exotic | 1902 | > > > > -- > > You received this message because you are subscribed to the Google > > Groups "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > > ## To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm_medium=email&utm_source=footer) . For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > You received this message because you are subscribed to a topic in the > > Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit > > [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to > > [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr)[https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm_medium=email&utm_source=footer). > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/fa4164f4-feb0-412d-81ea-953d59b2139c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/fa4164f4-feb0-412d-81ea-953d59b2139c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![Harry\_Waye\_2](https://avatars.discourse-cdn.com/v4/letter/h/b2d939/32.png) [@Harry\_Waye\_2](https://discuss.elastic.co/u/Harry_Waye_2) #### Post date: [March 25, 2014, 2:34am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/7 "2014-03-25T02:34:59Z")
Indeed sounds like [Field Collapsing/Combining 路 Issue #256 路 elastic/elasticsearch 路 GitHub](https://github.com/elasticsearch/elasticsearch/issues/256) Not easy to do. On Tuesday, March 25, 2014 2:20:42 AM UTC, Otis Gospodnetic wrote: > Hi David, > > I looked around and haven't been able to find any examples of bucket > aggregations other than terms one. I think the way this aggregation is > described makes it sound like group by in RDBMS world, but it looks like > that's not the case. > > Take this pseudo-example: > > SELECT category, city, makemodel > FROM cars > GROUP BY category TOP 2 > > In a DB world this could return the following: > > ======================================================================================== > | category | city | makemodel > | > > ======================================================================================== > | compact | u.s.a./texas/houston | asian/acura/tl > | > | compact | u.s.a./florida/tampa | asian/acura/1.6el > | > > +---------------+---------------------------------+------------------------------------+ > | exotic | u.s.a./florida/tampa | european/bentley/azure > | > | exotic | u.s.a./california/san francisco | european/bentley/azure > | > > ======================================================================================== > > Note how each row is a full row/document. I think this is the key part. > > Can ES aggregations do this? > If so, could you please share an example or point me to it? None of the > examples of bucket aggregates I saw showed this. > > As far as I can tell, the above cannot be done with ES aggregations > because it's essentially like "field collapsing" functionality that's not > in ES yet, but maybe I'm missing something... > > ## Thanks, Otis > > Performance Monitoring \* Log Analytics \* Search Analytics > Solr & Elasticsearch Support \* [http://sematext.com/](http://sematext.com/) > > On Thursday, March 20, 2014 5:36:22 AM UTC-4, David Pilato wrote: > > > Terms aggregation should be what you are looking for. > > > > -- > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr[https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > Le 20 mars 2014 脿 10:24:02, Nguyen Manh Tien ([tien.ng...@gmail.com](mailto:tien.ng...@gmail.com)) a > > 茅crit: > > > > Thanks David, > > > > If you check my sample above, i don't want any aggregate info (min, max, > > count, ...) for each group, just want to get top N result from each group, > > is this possible? > > > > On Thu, Mar 20, 2014 at 1:53 PM, David Pilato [da...@pilato.fr](mailto:da...@pilato.fr) wrote: > > > > > Have a look at aggregations. > > > > > > -- > > > David 馃槈 > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.ng...@gmail.com](mailto:tien.ng...@gmail.com) a > > > 茅crit : > > > > > > Hello, > > > > > > In SQL i can use GROUP BY to limit number of rows in each group like > > > bellow. > > > Is there any way to do that in Elasticsearch? > > > > > > Thanks. > > > > > > 1. > > > > > > _Find the first 10 groups of cars, with 2 hits in each group:_ > > > > > > SELECT category, city, makemodel > > > FROM cars > > > GROUP BY category TOP 2 > > > > > > _Results:_ > > > > > > # ======================================================================================== | category | city | makemodel | > > > > > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > > > 1. > > > > > > _Find the numbers of cars in different categories:_ > > > > > > SELECT category, grouphitscount > > > FROM cars > > > GROUP BY category top 1 > > > > > > _Results:_ > > > > > > +---------------+----------------+ > > > | category | grouphitscount | > > > +---------------+----------------+ > > > | compact | 4169 | > > > | exotic | 1902 | > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > ## To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm_medium=email&utm_source=footer) . For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > You received this message because you are subscribed to a topic in the > > > Google Groups "elasticsearch" group. > > > To unsubscribe from this topic, visit > > > [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > > > To unsubscribe from this group and all its topics, send an email to > > > [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr)[https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm_medium=email&utm_source=footer). > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![otisg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/otisg/32/492_2.png) [@otisg](https://discuss.elastic.co/u/otisg) #### Post date: [March 25, 2014, 3:02am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/8 "2014-03-25T03:02:24Z")
David was suggesting that this IS doable with ES aggregations, so I'd love to see if that is indeed so. ## Thanks, Otis Performance Monitoring \* Log Analytics \* Search Analytics Solr & Elasticsearch Support \* [http://sematext.com/](http://sematext.com/) On Mon, Mar 24, 2014 at 10:34 PM, Harry Waye [harry@arachnys.com](mailto:harry@arachnys.com) wrote: > Indeed sounds like > [Field Collapsing/Combining 路 Issue #256 路 elastic/elasticsearch 路 GitHub](https://github.com/elasticsearch/elasticsearch/issues/256) > > Not easy to do. > > On Tuesday, March 25, 2014 2:20:42 AM UTC, Otis Gospodnetic wrote: > > > Hi David, > > > > I looked around and haven't been able to find any examples of bucket > > aggregations other than terms one. I think the way this aggregation is > > described makes it sound like group by in RDBMS world, but it looks like > > that's not the case. > > > > Take this pseudo-example: > > > > SELECT category, city, makemodel > > FROM cars > > GROUP BY category TOP 2 > > > > In a DB world this could return the following: > > > > # ============================================================ > > > > # | category | city | makemodel | > > > > # ============================ | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+--------- ---------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > ============================ > > > > Note how each row is a full row/document. I think this is the key part. > > > > Can ES aggregations do this? > > If so, could you please share an example or point me to it? None of the > > examples of bucket aggregates I saw showed this. > > > > As far as I can tell, the above cannot be done with ES aggregations > > because it's essentially like "field collapsing" functionality that's not > > in ES yet, but maybe I'm missing something... > > > > ## Thanks, Otis > > > > Performance Monitoring \* Log Analytics \* Search Analytics > > Solr & Elasticsearch Support \* [http://sematext.com/](http://sematext.com/) > > > > On Thursday, March 20, 2014 5:36:22 AM UTC-4, David Pilato wrote: > > > > > Terms aggregation should be what you are looking for. > > > > > > -- > > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_ > > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr[https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) > > > > > > Le 20 mars 2014 脿 10:24:02, Nguyen Manh Tien ([tien.ng...@gmail.com](mailto:tien.ng...@gmail.com)) a > > > 茅crit: > > > > > > Thanks David, > > > > > > If you check my sample above, i don't want any aggregate info (min, max, > > > count, ...) for each group, just want to get top N result from each group, > > > is this possible? > > > > > > On Thu, Mar 20, 2014 at 1:53 PM, David Pilato [da...@pilato.fr](mailto:da...@pilato.fr) wrote: > > > > > > > Have a look at aggregations. > > > > > > > > -- > > > > David 馃槈 > > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > > > Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.ng...@gmail.com](mailto:tien.ng...@gmail.com) a > > > > 茅crit : > > > > > > > > Hello, > > > > > > > > In SQL i can use GROUP BY to limit number of rows in each group like > > > > bellow. > > > > Is there any way to do that in Elasticsearch? > > > > > > > > Thanks. > > > > > > > > 1. > > > > > > > > _Find the first 10 groups of cars, with 2 hits in each group:_ > > > > > > > > SELECT category, city, makemodel > > > > FROM cars > > > > GROUP BY category TOP 2 > > > > > > > > _Results:_ > > > > > > > > # ======================================================================================== | category | city | makemodel | > > > > > > > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > > > > > 1. > > > > > > > > _Find the numbers of cars in different categories:_ > > > > > > > > SELECT category, grouphitscount > > > > FROM cars > > > > GROUP BY category top 1 > > > > > > > > _Results:_ > > > > > > > > +---------------+----------------+ > > > > | category | grouphitscount | > > > > +---------------+----------------+ > > > > | compact | 4169 | > > > > | exotic | 1902 | > > > > > > > > -- > > > > You received this message because you are subscribed to the Google > > > > Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > > > ## To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c% [40googlegroups.com](http://40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com?utm_medium=email&utm_source=footer) . For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > You received this message because you are subscribed to a topic in the > > > > Google Groups "elasticsearch" group. > > > > To unsubscribe from this topic, visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > topic/elasticsearch/8GZHBLh1vsc/unsubscribe. > > > > To unsubscribe from this group and all its topics, send an email to > > > > [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%[40pilato.fr](http://40pilato.fr)[https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr?utm_medium=email&utm_source=footer). > > > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA% > > > 2BgJHhsz\_m0Erjd4h-sZJmp7Q%[40mail.gmail.com](http://40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > You received this message because you are subscribed to a topic in the > > > Google Groups "elasticsearch" group. > > > To unsubscribe from this topic, visit > > > [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > > > To unsubscribe from this group and all its topics, send an email to > > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CANNBgPKHufCmccQrCZq13zqjZ5cZYDLMxo12-%3Dkd6dqNB4qHOw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANNBgPKHufCmccQrCZq13zqjZ5cZYDLMxo12-%3Dkd6dqNB4qHOw%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet) #### Post date: [March 25, 2014, 6:08am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/9 "2014-03-25T06:08:14Z")
Sorry. I misread the question. By now we don't support it. -- David 馃槈 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 25 mars 2014 脿 04:02, Otis Gospodnetic [otis.gospodnetic@gmail.com](mailto:otis.gospodnetic@gmail.com) a 茅crit : David was suggesting that this IS doable with ES aggregations, so I'd love to see if that is indeed so. ## Thanks, Otis Performance Monitoring \* Log Analytics \* Search Analytics Solr & Elasticsearch Support \* [http://sematext.com/](http://sematext.com/) > On Mon, Mar 24, 2014 at 10:34 PM, Harry Waye [harry@arachnys.com](mailto:harry@arachnys.com) wrote: > Indeed sounds like [Field Collapsing/Combining 路 Issue #256 路 elastic/elasticsearch 路 GitHub](https://github.com/elasticsearch/elasticsearch/issues/256) > > Not easy to do. > > > On Tuesday, March 25, 2014 2:20:42 AM UTC, Otis Gospodnetic wrote: > > Hi David, > > > > I looked around and haven't been able to find any examples of bucket aggregations other than terms one. I think the way this aggregation is described makes it sound like group by in RDBMS world, but it looks like that's not the case. > > > > Take this pseudo-example: > > > > SELECT category, city, makemodel > > FROM cars > > GROUP BY category TOP 2 > > > > In a DB world this could return the following: > > > > # ======================================================================================== | category | city | makemodel | > > > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > Note how each row is a full row/document. I think this is the key part. > > > > Can ES aggregations do this? > > If so, could you please share an example or point me to it? None of the examples of bucket aggregates I saw showed this. > > > > As far as I can tell, the above cannot be done with ES aggregations because it's essentially like "field collapsing" functionality that's not in ES yet, but maybe I'm missing something... > > > > ## Thanks, Otis > > > > Performance Monitoring \* Log Analytics \* Search Analytics > > Solr & Elasticsearch Support \* [http://sematext.com/](http://sematext.com/) > > > > > On Thursday, March 20, 2014 5:36:22 AM UTC-4, David Pilato wrote: > > > Terms aggregation should be what you are looking for. > > > > > > -- > > > David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com) > > > @dadoonet | @elasticsearchfr > > > > > > > Le 20 mars 2014 脿 10:24:02, Nguyen Manh Tien ([tien.ng...@gmail.com](mailto:tien.ng...@gmail.com)) a 茅crit: > > > > > > > > Thanks David, > > > > > > > > If you check my sample above, i don't want any aggregate info (min, max, count, ...) for each group, just want to get top N result from each group, is this possible? > > > > > > > > > On Thu, Mar 20, 2014 at 1:53 PM, David Pilato [da...@pilato.fr](mailto:da...@pilato.fr) wrote: > > > > > Have a look at aggregations. > > > > > > > > > > -- > > > > > David 馃槈 > > > > > Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs > > > > > > > > > > Le 20 mars 2014 脿 03:55, Nguyen Manh Tien [tien.ng...@gmail.com](mailto:tien.ng...@gmail.com) a 茅crit : > > > > > > > > > > Hello, > > > > > > > > > > In SQL i can use GROUP BY to limit number of rows in each group like bellow. > > > > > Is there any way to do that in Elasticsearch? > > > > > > > > > > Thanks. > > > > > Find the first 10 groups of cars, with 2 hits in each group: > > > > > > > > > > SELECT category, city, makemodel > > > > > FROM cars > > > > > GROUP BY category TOP 2 > > > > > Results: > > > > > > > > > > # ======================================================================================== | category | city | makemodel | > > > > > > > > > > # | compact | u.s.a./texas/houston | asian/acura/tl | | compact | u.s.a./florida/tampa | asian/acura/1.6el | +---------------+---------------------------------+------------------------------------+ | exotic | u.s.a./florida/tampa | european/bentley/azure | | exotic | u.s.a./california/san francisco | european/bentley/azure | > > > > > > > > > > Find the numbers of cars in different categories: > > > > > > > > > > SELECT category, grouphitscount > > > > > FROM cars > > > > > GROUP BY category top 1 > > > > > Results: > > > > > > > > > > +---------------+----------------+ > > > > > | category | grouphitscount | > > > > > +---------------+----------------+ > > > > > | compact | 4169 | > > > > > | exotic | 1902 | > > > > > > > > > > -- > > > > > You received this message because you are subscribed to the Google Groups "elasticsearch" group. > > > > > To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > > > > > ## To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > > > You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. > > > > > To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > > > > > To unsubscribe from this group and all its topics, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr). > > > > > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > -- > > > > You received this message because you are subscribed to the Google Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb\_rbUHdEG8X\_jQDA%2BgJHhsz\_m0Erjd4h-sZJmp7Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.com). > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/8GZHBLh1vsc/unsubscribe). > To unsubscribe from this group and all its topics, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com). > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CANNBgPKHufCmccQrCZq13zqjZ5cZYDLMxo12-%3Dkd6dqNB4qHOw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CANNBgPKHufCmccQrCZq13zqjZ5cZYDLMxo12-%3Dkd6dqNB4qHOw%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/0DB0DFAA-130B-42B9-BEAD-8B2C19A3586C%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/0DB0DFAA-130B-42B9-BEAD-8B2C19A3586C%40pilato.fr). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
### Author: ![jogaco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jogaco/32/4722_2.png) [@jogaco](https://discuss.elastic.co/u/jogaco) #### Post date: [September 11, 2015, 9:58am UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/10 "2015-09-11T09:58:51Z")
In case anyone is searching for this, I think this can be done with the [top hits aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html) ---
### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system) #### Post date: [July 5, 2017, 11:50pm UTC](https://discuss.elastic.co/t/group-by-query-in-elasticsearch/16483/11 "2017-07-05T23:50:48Z")