# Nested aggregation against key value pairs **URL:** \ **Category:** Elasticsearch\ **Created:** [October 24, 2014, 7:17pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425 "2014-10-24T19:17:04Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Jay\_Hilden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jay_hilden/32/877_2.png) [@Jay\_Hilden](https://discuss.elastic.co/u/Jay_Hilden)\ **Post date:** [October 24, 2014, 7:17pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/1 "2014-10-24T19:17:04Z")
I have an ES type with a nested KeyValuePair type. What I'm trying to do is a terms aggregation on both the key and value fields such that I'd get the following results: Key1 - Value1: DocCount = 10 Key1 - Value2: DocCount = 9 Key2 - Value3: DocCount = 4 Here is my mapping: { "index123" : { "mappings" : { "type123" : { "properties" : { "authEventID" : { "type" : "long" }, "authInput" : { "properties" : { "uIDExtensionFields" : { "type" : "nested", "properties" : { "key" : { "type" : "string" }, "value" : { "type" : "string" } } } } } } } } } } Is there a way to do this? Thank you. -- 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/c8c637c0-3933-4b1b-ad32-0c8bfe9485bd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c8c637c0-3933-4b1b-ad32-0c8bfe9485bd%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Zennet\_Wheatcroft](https://avatars.discourse-cdn.com/v4/letter/z/8491ac/32.png) [@Zennet\_Wheatcroft](https://discuss.elastic.co/u/Zennet_Wheatcroft)\ **Post date:** [October 24, 2014, 10:39pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/2 "2014-10-24T22:39:11Z")
Have you tried the usual sub-aggregations? It looks like it should do exactly what you want. If so, why does that not work? Can you include some sample data and queries you have tried so that we can index it and try your queries? "Bucketing aggregations can have sub-aggregations (bucketing or metric). The sub-aggregations will be computed for the buckets which their parent aggregation generates." > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. On Friday, October 24, 2014 12:17:04 PM UTC-7, Jay Hilden wrote: > I have an ES type with a nested KeyValuePair type. What I'm trying to do > is a terms aggregation on both the key and value fields such that I'd get > the following results: > > Key1 - Value1: DocCount = 10 > Key1 - Value2: DocCount = 9 > Key2 - Value3: DocCount = 4 > > Here is my mapping: > { > "index123" : { > "mappings" : { > "type123" : { > "properties" : { > "authEventID" : { > "type" : "long" > }, > "authInput" : { > "properties" : { > "uIDExtensionFields" : { > "type" : "nested", > "properties" : { > "key" : { > "type" : "string" > }, > "value" : { > "type" : "string" > } > } > } > } > } > } > } > } > } > } > > Is there a way to do this? > > Thank you. -- 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/5415a7f5-31ea-4085-af3a-0bbbdc875ea9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/5415a7f5-31ea-4085-af3a-0bbbdc875ea9%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Jay\_Hilden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jay_hilden/32/877_2.png) [@Jay\_Hilden](https://discuss.elastic.co/u/Jay_Hilden)\ **Post date:** [October 27, 2014, 12:52pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/3 "2014-10-27T12:52:46Z")
Here is some sample data: PUT index1 PUT index1/type1/\_mapping { "type1": { "properties": { "kvp": { "type": "nested", "properties": { "key": { "type": "string" }, "value": { "type": "string" } } } } } } POST index1/type1 { "kvp": [ { "key": "key1", "value": "value1" } ] } POST index1/type1 { "kvp": [ { "key": "key1", "value": "value1" }, { "key": "key1", "value": "value2" }, { "key": "key2", "value": "value1" }, { "key": "key2", "value": "value2" } ] } The result I'd like combines the fields "kvp.key" and "kvp.value": key1 - value1: DocCount = 2 key1 - value2: DocCount = 1 key2 - value2: DocCount = 1 I'm starting to think that I need to re-index the data and combine the "kvp.key" and "kvp.value" fields into a single field so that I can aggregate on it. On Friday, October 24, 2014 2:17:04 PM UTC-5, Jay Hilden wrote: > I have an ES type with a nested KeyValuePair type. What I'm trying to do > is a terms aggregation on both the key and value fields such that I'd get > the following results: > > Key1 - Value1: DocCount = 10 > Key1 - Value2: DocCount = 9 > Key2 - Value3: DocCount = 4 > > Here is my mapping: > { > "index123" : { > "mappings" : { > "type123" : { > "properties" : { > "authEventID" : { > "type" : "long" > }, > "authInput" : { > "properties" : { > "uIDExtensionFields" : { > "type" : "nested", > "properties" : { > "key" : { > "type" : "string" > }, > "value" : { > "type" : "string" > } > } > } > } > } > } > } > } > } > } > > Is there a way to do this? > > Thank you. -- 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/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\ **Post date:** [October 27, 2014, 5:12pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/4 "2014-10-27T17:12:51Z")
Hi Jay, Reindexing and having a key that combines the key and value fields would certainly be the fastest option. On Mon, Oct 27, 2014 at 1:52 PM, Jay Hilden [jay.hilden@gmail.com](mailto:jay.hilden@gmail.com) wrote: > Here is some sample data: > > PUT index1 > > PUT index1/type1/\_mapping > { > "type1": { > "properties": { > "kvp": { > "type": "nested", > "properties": { > "key": { > "type": "string" > }, > "value": { > "type": "string" > } > } > } > } > } > } > > POST index1/type1 > { > "kvp": [ > { > "key": "key1", > "value": "value1" > } > ] > } > > POST index1/type1 > { > "kvp": [ > { > "key": "key1", > "value": "value1" > }, > { > "key": "key1", > "value": "value2" > }, > { > "key": "key2", > "value": "value1" > }, > { > "key": "key2", > "value": "value2" > } > ] > } > > The result I'd like combines the fields "kvp.key" and "kvp.value": > key1 - value1: DocCount = 2 > key1 - value2: DocCount = 1 > key2 - value2: DocCount = 1 > > I'm starting to think that I need to re-index the data and combine the > "kvp.key" and "kvp.value" fields into a single field so that I can > aggregate on it. > > On Friday, October 24, 2014 2:17:04 PM UTC-5, Jay Hilden wrote: > > > I have an ES type with a nested KeyValuePair type. What I'm trying to do > > is a terms aggregation on both the key and value fields such that I'd get > > the following results: > > > > Key1 - Value1: DocCount = 10 > > Key1 - Value2: DocCount = 9 > > Key2 - Value3: DocCount = 4 > > > > Here is my mapping: > > { > > "index123" : { > > "mappings" : { > > "type123" : { > > "properties" : { > > "authEventID" : { > > "type" : "long" > > }, > > "authInput" : { > > "properties" : { > > "uIDExtensionFields" : { > > "type" : "nested", > > "properties" : { > > "key" : { > > "type" : "string" > > }, > > "value" : { > > "type" : "string" > > } > > } > > } > > } > > } > > } > > } > > } > > } > > } > > > > Is there a way to do this? > > > > Thank you. > > -- > 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/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- Adrien Grand -- 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/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF\_D3QJPNjsHQGMcQw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF_D3QJPNjsHQGMcQw%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Jay\_Hilden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jay_hilden/32/877_2.png) [@Jay\_Hilden](https://discuss.elastic.co/u/Jay_Hilden)\ **Post date:** [October 27, 2014, 5:16pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/5 "2014-10-27T17:16:05Z")
Thanks Adrien. On Mon, Oct 27, 2014 at 12:12 PM, Adrien Grand \< [adrien.grand@elasticsearch.com](mailto:adrien.grand@elasticsearch.com)\> wrote: > Hi Jay, > > Reindexing and having a key that combines the key and value fields would > certainly be the fastest option. > > On Mon, Oct 27, 2014 at 1:52 PM, Jay Hilden [jay.hilden@gmail.com](mailto:jay.hilden@gmail.com) wrote: > > > Here is some sample data: > > > > PUT index1 > > > > PUT index1/type1/\_mapping > > { > > "type1": { > > "properties": { > > "kvp": { > > "type": "nested", > > "properties": { > > "key": { > > "type": "string" > > }, > > "value": { > > "type": "string" > > } > > } > > } > > } > > } > > } > > > > POST index1/type1 > > { > > "kvp": [ > > { > > "key": "key1", > > "value": "value1" > > } > > ] > > } > > > > POST index1/type1 > > { > > "kvp": [ > > { > > "key": "key1", > > "value": "value1" > > }, > > { > > "key": "key1", > > "value": "value2" > > }, > > { > > "key": "key2", > > "value": "value1" > > }, > > { > > "key": "key2", > > "value": "value2" > > } > > ] > > } > > > > The result I'd like combines the fields "kvp.key" and "kvp.value": > > key1 - value1: DocCount = 2 > > key1 - value2: DocCount = 1 > > key2 - value2: DocCount = 1 > > > > I'm starting to think that I need to re-index the data and combine the > > "kvp.key" and "kvp.value" fields into a single field so that I can > > aggregate on it. > > > > On Friday, October 24, 2014 2:17:04 PM UTC-5, Jay Hilden wrote: > > > > > I have an ES type with a nested KeyValuePair type. What I'm trying to > > > do is a terms aggregation on both the key and value fields such that I'd > > > get the following results: > > > > > > Key1 - Value1: DocCount = 10 > > > Key1 - Value2: DocCount = 9 > > > Key2 - Value3: DocCount = 4 > > > > > > Here is my mapping: > > > { > > > "index123" : { > > > "mappings" : { > > > "type123" : { > > > "properties" : { > > > "authEventID" : { > > > "type" : "long" > > > }, > > > "authInput" : { > > > "properties" : { > > > "uIDExtensionFields" : { > > > "type" : "nested", > > > "properties" : { > > > "key" : { > > > "type" : "string" > > > }, > > > "value" : { > > > "type" : "string" > > > } > > > } > > > } > > > } > > > } > > > } > > > } > > > } > > > } > > > } > > > > > > Is there a way to do this? > > > > > > Thank you. > > > > -- > > 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/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > Adrien Grand > > -- > 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/NJRzaH6FUfY/unsubscribe](https://groups.google.com/d/topic/elasticsearch/NJRzaH6FUfY/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/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF\_D3QJPNjsHQGMcQw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF_D3QJPNjsHQGMcQw%40mail.gmail.com) > [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF\_D3QJPNjsHQGMcQw%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF_D3QJPNjsHQGMcQw%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/CAD3qxy53ggTWrK4taryaLe7jPu%3DSPJMNeUzbnzbO%2B%3D\_EzbdTBQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAD3qxy53ggTWrK4taryaLe7jPu%3DSPJMNeUzbnzbO%2B%3D_EzbdTBQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Zennet\_Wheatcroft](https://avatars.discourse-cdn.com/v4/letter/z/8491ac/32.png) [@Zennet\_Wheatcroft](https://discuss.elastic.co/u/Zennet_Wheatcroft)\ **Post date:** [October 27, 2014, 7:27pm UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/6 "2014-10-27T19:27:53Z")
Try this query: GET index1/type1/\_search?pretty { "size":0, "aggs":{ "ag1":{ "nested":{ "path":"kvp" }, "aggs":{ "keyagg":{ "terms":{ "field":"key" }, "aggs":{ "valagg":{ "terms":{ "field":"value" } } } } } } } } Result: { "aggregations":{ "ag1":{ "doc\_count":5, "keyagg":{ "buckets":[ { "key":"key1", "doc\_count":3, "valagg":{ "buckets":[ { "key":"value1", "doc\_count":2 }, { "key":"value2", "doc\_count":1 } ] } }, { "key":"key2", "doc\_count":2, "valagg":{ "buckets":[ { "key":"value1", "doc\_count":1 }, { "key":"value2", "doc\_count":1 } ] } } ] } } } } Though I'm not sure why you are using the nested type. I think it would be easier and more efficient to flatten it to one document per event and extension field. e.g. {"authEventID": "abc", "authInput":{ "key":"key1", "value"value1"}} {"authEventID": "abc", "authInput":{ "key":"key1", "value"value1"}} {"authEventID": "abc", "authInput":{ "key":"key1", "value"value2"}} {"authEventID": "abc", "authInput":{ "key":"key2", "value"value2"}} ... {"authEventID": "def", "authInput":{ "key":"key1", "value"value1"}} {"authEventID": "def", "authInput":{ "key":"key2", "value"value2"}} {"authEventID": "def", "authInput":{ "key":"key3", "value"value1"}} On Monday, October 27, 2014 5:52:46 AM UTC-7, Jay Hilden wrote: > Here is some sample data: > > PUT index1 > > PUT index1/type1/\_mapping > { > "type1": { > "properties": { > "kvp": { > "type": "nested", > "properties": { > "key": { > "type": "string" > }, > "value": { > "type": "string" > } > } > } > } > } > } > > POST index1/type1 > { > "kvp": [ > { > "key": "key1", > "value": "value1" > } > ] > } > > POST index1/type1 > { > "kvp": [ > { > "key": "key1", > "value": "value1" > }, > { > "key": "key1", > "value": "value2" > }, > { > "key": "key2", > "value": "value1" > }, > { > "key": "key2", > "value": "value2" > } > ] > } > > The result I'd like combines the fields "kvp.key" and "kvp.value": > key1 - value1: DocCount = 2 > key1 - value2: DocCount = 1 > key2 - value2: DocCount = 1 > > I'm starting to think that I need to re-index the data and combine the > "kvp.key" and "kvp.value" fields into a single field so that I can > aggregate on it. > > On Friday, October 24, 2014 2:17:04 PM UTC-5, Jay Hilden wrote: > > > I have an ES type with a nested KeyValuePair type. What I'm trying to do > > is a terms aggregation on both the key and value fields such that I'd get > > the following results: > > > > Key1 - Value1: DocCount = 10 > > Key1 - Value2: DocCount = 9 > > Key2 - Value3: DocCount = 4 > > > > Here is my mapping: > > { > > "index123" : { > > "mappings" : { > > "type123" : { > > "properties" : { > > "authEventID" : { > > "type" : "long" > > }, > > "authInput" : { > > "properties" : { > > "uIDExtensionFields" : { > > "type" : "nested", > > "properties" : { > > "key" : { > > "type" : "string" > > }, > > "value" : { > > "type" : "string" > > } > > } > > } > > } > > } > > } > > } > > } > > } > > } > > > > Is there a way to do this? > > > > Thank you. -- 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/a9993d74-571d-4f5d-bb51-b83c1070035b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a9993d74-571d-4f5d-bb51-b83c1070035b%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 12:53am UTC](https://discuss.elastic.co/t/nested-aggregation-against-key-value-pairs/20425/7 "2017-07-06T00:53:37Z")