GROUP BY query in ElasticSearch

Elastic Stack Elasticsearch
1

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.fr.
For more options, visit https://groups.google.com/d/optout.

3

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 wrote:

Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.comhttps://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.

--
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.
To unsubscribe from this group and all its topics, send an email to
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.frhttps://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.

--
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.
To view this discussion on the web visit 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.

4

Terms aggregation should be what you are looking for.

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr

Le 20 mars 2014 à 10:24:02, Nguyen Manh Tien (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 wrote:
Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.

To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com.
For more options, visit 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.
To unsubscribe from this group and all its topics, send an email to 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.

For more options, visit 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.
To view this discussion on the web visit 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.

--
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.
To view this discussion on the web visit 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.

5

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 wrote:

Terms aggregation should be what you are looking for.

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet https://twitter.com/dadoonet | @elasticsearchfrhttps://twitter.com/elasticsearchfr

Le 20 mars 2014 à 10:24:02, Nguyen Manh Tien (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 wrote:

Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.comhttps://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.

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.
To unsubscribe from this group and all its topics, send an email to
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.frhttps://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.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.comhttps://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.

--
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.
To unsubscribe from this group and all its topics, send an email to
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.localhttps://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.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CANihJfXAHRwDP9T1%2BTsUR24CGqv3MQVBnFgTD9Q6imApG_R37Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

6

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/

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfrhttps://twitter.com/elasticsearchfr

Le 20 mars 2014 à 10:24:02, Nguyen Manh Tien (tien.ng...@gmail.com<javascript:>)
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<javascript:>

wrote:

Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien <tien.ng...@gmail.com<javascript:>>
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 <javascript:>.

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.comhttps://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.

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.
To unsubscribe from this group and all its topics, send an email to
elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.frhttps://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.

--
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 <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.comhttps://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.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/fa4164f4-feb0-412d-81ea-953d59b2139c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

7

Indeed sounds like Field Collapsing/Combining · Issue #256 · elastic/elasticsearch · GitHub

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/

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfrhttps://twitter.com/elasticsearchfr

Le 20 mars 2014 à 10:24:02, Nguyen Manh Tien (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 wrote:

Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.comhttps://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.

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.
To unsubscribe from this group and all its topics, send an email to
elasticsearc...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.frhttps://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.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.comhttps://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.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/5542fac6-59b1-4033-805c-170e6d500ec9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

8

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/

On Mon, Mar 24, 2014 at 10:34 PM, Harry Waye harry@arachnys.com wrote:

Indeed sounds like
Field Collapsing/Combining · Issue #256 · elastic/elasticsearch · GitHub

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/

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfrhttps://twitter.com/elasticsearchfr

Le 20 mars 2014 à 10:24:02, Nguyen Manh Tien (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 wrote:

Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.

To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%
40googlegroups.comhttps://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.

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.
To unsubscribe from this group and all its topics, send an email to
elasticsearc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/FA25DC0F-39A9-4131-B6FF-F0EEC4E6C99C%40pilato.frhttps://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.

--
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.
To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/CANihJfWFQZb_rbUHdEG8X_jQDA%
2BgJHhsz_m0Erjd4h-sZJmp7Q%40mail.gmail.comhttps://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.

--
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.
To unsubscribe from this group and all its topics, send an email to
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.comhttps://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.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CANNBgPKHufCmccQrCZq13zqjZ5cZYDLMxo12-%3Dkd6dqNB4qHOw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

9

Sorry. I misread the question. By now we don't support it.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 25 mars 2014 à 04:02, Otis Gospodnetic 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/

On Mon, Mar 24, 2014 at 10:34 PM, Harry Waye harry@arachnys.com wrote:
Indeed sounds like Field Collapsing/Combining · Issue #256 · elastic/elasticsearch · GitHub

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/

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
@dadoonet | @elasticsearchfr

Le 20 mars 2014 à 10:24:02, Nguyen Manh Tien (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 wrote:
Have a look at aggregations.

--
David :wink:
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 mars 2014 à 03:55, Nguyen Manh Tien 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.

To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c1e2d776-e006-430c-82a0-389e3fc6731c%40googlegroups.com.
For more options, visit 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.
To unsubscribe from this group and all its topics, send an email to 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.

For more options, visit 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.
To view this discussion on the web visit 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.

--
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.
To unsubscribe from this group and all its topics, send an email to 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.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CANNBgPKHufCmccQrCZq13zqjZ5cZYDLMxo12-%3Dkd6dqNB4qHOw%40mail.gmail.com.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/0DB0DFAA-130B-42B9-BEAD-8B2C19A3586C%40pilato.fr.
For more options, visit https://groups.google.com/d/optout.

10

In case anyone is searching for this, I think this can be done with the top hits aggregation

1 Like