Nested aggregation against key value pairs

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/c8c637c0-3933-4b1b-ad32-0c8bfe9485bd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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."
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations.html

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/5415a7f5-31ea-4085-af3a-0bbbdc875ea9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/d9db4ea8-68af-4cc5-a6dc-876f218b58f7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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 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.
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?utm_medium=email&utm_source=footer
.

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

Thanks Adrien.

On Mon, Oct 27, 2014 at 12:12 PM, Adrien Grand <
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 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.
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?utm_medium=email&utm_source=footer
.

For more options, visit 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.
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/CAL6Z4j7j2Shpz1G7GhpFA5Oqy8ReCWTHrF_D3QJPNjsHQGMcQw%40mail.gmail.com
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.

--
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/CAD3qxy53ggTWrK4taryaLe7jPu%3DSPJMNeUzbnzbO%2B%3D_EzbdTBQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a9993d74-571d-4f5d-bb51-b83c1070035b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.