# Multi Field Aggregation
**URL:** https://discuss.elastic.co/t/multi-field-aggregation/20286
**Category:** Elasticsearch
**Created:** [October 16, 2014, 12:57pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286 "2014-10-16T12:57:35Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Alastair\_James](https://discuss.elastic.co/u/Alastair_James)
#### Post date: [October 16, 2014, 12:57pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/1 "2014-10-16T12:57:35Z")
Hi there.
I am trying to create an aggregation that mimics the following SQL query:
SELECT col1, col2, COUNT(\*), SUM(metric) FROM table GROUP BY col1, col2
ORDER BY SUM(metric) DESC
On the face of it, I could create an terms aggregation for col1, add a
terms aggregation for col2 inside it, and the metric aggregations inside
that. I could then dynamically build the SQL result like grid and sort it
myself. However this breaks down for large results set, or a paginated
result set of a larger result.
The problem is that the ES aggregation system always returns the top N
results for each parent and child bucket. Thus for each value of col1 I
have N values of col2.
What I really want is to consider all possible combinations of col1 and
col2 in the same way as SQL does it and return the top N based on some
other metric. E.g. in ES speak, a single aggregation where the keys are
tuples of (col1, col2).
I suppose one way would be to use a script terms aggregation to concatenate
each value of col1 and col2, however thats going to be slow.
Does anyone else have any ideas?
Ideally there would be a tuple aggregation built in, e.g.:
"my\_agg":{
"tuple":{
"fields":["col1","col2"]
}
}
Would product keys that are objects like:
{
"col1":"value1",
"col2":"value2"
}
Does anyone know if this would be possible to write as a plugin?
--
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/9ec80e84-17b0-435d-a02e-f56a5d49f733%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9ec80e84-17b0-435d-a02e-f56a5d49f733%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Artur\_Martins](https://discuss.elastic.co/u/Artur_Martins)
#### Post date: [October 17, 2014, 11:44pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/2 "2014-10-17T23:44:48Z")
Hello,
I'm having the exact same problem.
Have you managed to find a solution?
My thread is here: LINK
[https://groups.google.com/forum/?fromgroups#!topic/elasticsearch/Oum03VSBzHQ](https://groups.google.com/forum/?fromgroups#!topic/elasticsearch/Oum03VSBzHQ)
Thanks
On Thursday, October 16, 2014 1:57:35 PM UTC+1, Alastair James wrote:
> Hi there.
>
> I am trying to create an aggregation that mimics the following SQL query:
>
> SELECT col1, col2, COUNT(\*), SUM(metric) FROM table GROUP BY col1, col2
> ORDER BY SUM(metric) DESC
>
> On the face of it, I could create an terms aggregation for col1, add a
> terms aggregation for col2 inside it, and the metric aggregations inside
> that. I could then dynamically build the SQL result like grid and sort it
> myself. However this breaks down for large results set, or a paginated
> result set of a larger result.
>
> The problem is that the ES aggregation system always returns the top N
> results for each parent and child bucket. Thus for each value of col1 I
> have N values of col2.
>
> What I really want is to consider all possible combinations of col1 and
> col2 in the same way as SQL does it and return the top N based on some
> other metric. E.g. in ES speak, a single aggregation where the keys are
> tuples of (col1, col2).
>
> I suppose one way would be to use a script terms aggregation to
> concatenate each value of col1 and col2, however thats going to be slow.
>
> Does anyone else have any ideas?
>
> Ideally there would be a tuple aggregation built in, e.g.:
>
> "my\_agg":{
> "tuple":{
> "fields":["col1","col2"]
> }
> }
>
> Would product keys that are objects like:
>
> {
> "col1":"value1",
> "col2":"value2"
> }
>
> Does anyone know if this would be possible to write as a plugin?
--
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/713127be-b89e-42ee-8811-18dd0e31d16a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/713127be-b89e-42ee-8811-18dd0e31d16a%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Alastair\_James](https://discuss.elastic.co/u/Alastair_James)
#### Post date: [October 19, 2014, 3:25pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/3 "2014-10-19T15:25:09Z")
Hi there.
No, no solution yet. I suspect its not possible in ES as it stands. It may
require a custom aggregation plugin to be written, or to use scripting.
On Saturday, 18 October 2014 00:44:48 UTC+1, Artur Martins wrote:
> Hello,
>
> I'm having the exact same problem.
> Have you managed to find a solution?
>
> My thread is here: LINK
> [https://groups.google.com/forum/?fromgroups#!topic/elasticsearch/Oum03VSBzHQ](https://groups.google.com/forum/?fromgroups#!topic/elasticsearch/Oum03VSBzHQ)
>
> Thanks
>
> On Thursday, October 16, 2014 1:57:35 PM UTC+1, Alastair James wrote:
>
> > Hi there.
> >
> > I am trying to create an aggregation that mimics the following SQL query:
> >
> > SELECT col1, col2, COUNT(\*), SUM(metric) FROM table GROUP BY col1, col2
> > ORDER BY SUM(metric) DESC
> >
> > On the face of it, I could create an terms aggregation for col1, add a
> > terms aggregation for col2 inside it, and the metric aggregations inside
> > that. I could then dynamically build the SQL result like grid and sort it
> > myself. However this breaks down for large results set, or a paginated
> > result set of a larger result.
> >
> > The problem is that the ES aggregation system always returns the top N
> > results for each parent and child bucket. Thus for each value of col1 I
> > have N values of col2.
> >
> > What I really want is to consider all possible combinations of col1 and
> > col2 in the same way as SQL does it and return the top N based on some
> > other metric. E.g. in ES speak, a single aggregation where the keys are
> > tuples of (col1, col2).
> >
> > I suppose one way would be to use a script terms aggregation to
> > concatenate each value of col1 and col2, however thats going to be slow.
> >
> > Does anyone else have any ideas?
> >
> > Ideally there would be a tuple aggregation built in, e.g.:
> >
> > "my\_agg":{
> > "tuple":{
> > "fields":["col1","col2"]
> > }
> > }
> >
> > Would product keys that are objects like:
> >
> > {
> > "col1":"value1",
> > "col2":"value2"
> > }
> >
> > Does anyone know if this would be possible to write as a plugin?
--
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/497c90a2-1625-440f-a004-383dd3872703%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/497c90a2-1625-440f-a004-383dd3872703%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Artur\_Martins](https://discuss.elastic.co/u/Artur_Martins)
#### Post date: [October 19, 2014, 3:48pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/4 "2014-10-19T15:48:07Z")
I heard that it could be done with a fingerprint, but I don't know how to do this. It's in logstash.conf
Have a look:
Fingerprint the 3-tuple of source address, destination address, destination port
if [SourceAddress] and [DestinationAddress] {
fingerprint {
concatenate\_sources =\> true
method =\> "SHA1"
key =\> "logstash"
source =\> ["SourceAddress", "DestinationAddress", "DestinationPort"]
}
}
But what exactly will this do? What next?
Hope you can understand this and help us both 😊
Thanks
--
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/005d8152-9ee0-49bb-a8d5-84ccb9634124%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/005d8152-9ee0-49bb-a8d5-84ccb9634124%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Alastair\_James](https://discuss.elastic.co/u/Alastair_James)
#### Post date: [October 19, 2014, 4:06pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/5 "2014-10-19T16:06:40Z")
Hmmm. I dont know much about logstash, but I suspect thats concatenating
the 3 values into one string and taking a hash of it.... This would allow
you to group by that exact set of 3 columns.... however my use case is that
I need to be able to group by and subset of columns, so this could not be
pre-defined in that way.
Al
On 19 October 2014 16:48, Artur Martins [arturphk@gmail.com](mailto:arturphk@gmail.com) wrote:
> I heard that it could be done with a fingerprint, but I don't know how to
> do this. It's in logstash.conf
>
> Have a look:
>
> Fingerprint the 3-tuple of source address, destination address,
> destination port
>
> if [SourceAddress] and [DestinationAddress] {
> fingerprint {
> concatenate\_sources =\> true
> method =\> "SHA1"
> key =\> "logstash"
> source =\> ["SourceAddress", "DestinationAddress", "DestinationPort"]
> }
> }
>
> But what exactly will this do? What next?
> Hope you can understand this and help us both 😊
>
> Thanks
>
> --
> 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/gVLNqArGvVA/unsubscribe](https://groups.google.com/d/topic/elasticsearch/gVLNqArGvVA/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/005d8152-9ee0-49bb-a8d5-84ccb9634124%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/005d8152-9ee0-49bb-a8d5-84ccb9634124%40googlegroups.com)
> .
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
Dr Alastair James
CTO [Ometria.com](http://Ometria.com)
Skype: al.james
--
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/CAMuyCY-u%2B6A%3DRUB1420BQZLt440eAShhSMeiwPWLLJgtq-Bm%3Dg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAMuyCY-u%2B6A%3DRUB1420BQZLt440eAShhSMeiwPWLLJgtq-Bm%3Dg%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Artur\_Martins](https://discuss.elastic.co/u/Artur_Martins)
#### Post date: [October 19, 2014, 9:01pm UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/6 "2014-10-19T21:01:54Z")
Yup, that's true. It will be able to query only by that set of columns,
which is an issue for future requirements.
For now its a quick-fix, but I wonder if I'm missing something on the
"aggregations" function..
Cheers
On Sunday, October 19, 2014 5:07:31 PM UTC+1, Alastair James wrote:
> Hmmm. I dont know much about logstash, but I suspect thats concatenating
> the 3 values into one string and taking a hash of it.... This would allow
> you to group by that exact set of 3 columns.... however my use case is that
> I need to be able to group by and subset of columns, so this could not be
> pre-defined in that way.
>
> Al
>
> On 19 October 2014 16:48, Artur Martins \<[artu...@gmail.com](mailto:artu...@gmail.com) \\>
> wrote:
>
> > I heard that it could be done with a fingerprint, but I don't know how to
> > do this. It's in logstash.conf
> >
> > Have a look:
> >
> > Fingerprint the 3-tuple of source address, destination address,
> > destination port
> >
> > if [SourceAddress] and [DestinationAddress] {
> > fingerprint {
> > concatenate\_sources =\> true
> > method =\> "SHA1"
> > key =\> "logstash"
> > source =\> ["SourceAddress", "DestinationAddress", "DestinationPort"]
> > }
> > }
> >
> > But what exactly will this do? What next?
> > Hope you can understand this and help us both 😊
> >
> > Thanks
> >
> > --
> > 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/gVLNqArGvVA/unsubscribe](https://groups.google.com/d/topic/elasticsearch/gVLNqArGvVA/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/005d8152-9ee0-49bb-a8d5-84ccb9634124%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/005d8152-9ee0-49bb-a8d5-84ccb9634124%40googlegroups.com)
> > .
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
>
> --
> Dr Alastair James
> CTO [Ometria.com](http://Ometria.com)
> Skype: al.james
--
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/f23f37e7-35a3-4a8a-9c8b-9334460f7aa7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/f23f37e7-35a3-4a8a-9c8b-9334460f7aa7%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 12:55am UTC](https://discuss.elastic.co/t/multi-field-aggregation/20286/7 "2017-07-06T00:55:13Z")