Group Values with Aggregation

[code_blue]

I am trying to write an aggregation query but not sure what I am missing here. I have the following structure across multiple documents. Some of the field names may not be present in documents.

{
	"CaptureData": [{
			
			"FieldName": "A",
			"FieldValue": [
				"xyz"
			]
		},
		{
		
			"FieldName": "B",
			"FieldValue": [
				"pqr"
			]
		}]
}

I am able to aggregate on different field names, but I want to also aggregate values for each field bucket. So I want to create a bucket for A and see list of values under A.

[abdon (Abdon Pijpelink)]

How have you mapped your data? You will need to map CaptureData as a nested field in order to do this.

Once you have done that, you will be able to execute a nested aggregation on CaptureData. Inside that nested aggregation, you can use a terms aggregation to get a set of buckets for FieldName and inside of that a terms aggregation on FieldValue to get a list of values under each A, B, etc.

[code_blue]

This is what I have in mapping:

  {
	"CaptureData": {
		"properties": {
			"FieldName": {
				"type": "text",
				"fields": {
					"keyword": {
						"type": "keyword",
						"ignore_above": 256
					}
				}
			},
			"FieldValue": {
				"type": "text",
				"fields": {
					"keyword": {
						"type": "keyword",
						"ignore_above": 256
					}
				}
			}
		}
	}
}

[abdon (Abdon Pijpelink)]

Alright, you did not map CaptureData as type nested. When creating the index apply the following mapping instead (the only difference is for CaptureData):

PUT my_index
{
  "mappings": {
    "properties": {
      "CaptureData": {
        "type": "nested",
        "properties": {
          "FieldName": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "FieldValue": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}

Next, you can index a document like the one your shared in your original post:

PUT my_index/_doc/1
{
  "CaptureData": [
    {
      "FieldName": "A",
      "FieldValue": [
        "xyz"
      ]
    },
    {
      "FieldName": "B",
      "FieldValue": [
        "pqr"
      ]
    }
  ]
}

And execute the nested aggregation I mentioned earlier:

GET my_index/_search
{
  "size": 0,
  "aggs": {
    "my_nested": {
      "nested": {
        "path": "CaptureData"
      },
      "aggs": {
        "field_names": {
          "terms": {
            "field": "CaptureData.FieldName.keyword",
            "size": 10
          },
          "aggs": {
            "field_values": {
              "terms": {
                "field": "CaptureData.FieldValue.keyword",
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

[code_blue]

Getting the following error:

Root mapping definition has unsupported parameters: [CaptureData : {type=nested, properties={FieldValue={type=text, fields={keyword={ignore_above=256, type=keyword}}}, FieldName={type=text, fields={keyword={ignore_above=256, type=keyword}

[abdon (Abdon Pijpelink)]

What version of Elasticsearch are you using? If you're still on version 6, the request would be:

PUT my_index
{
  "mappings": {
    "_doc": {
      "properties": {
        "CaptureData": {
          "type": "nested",
          "properties": {
            "FieldName": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "FieldValue": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        }
      }
    }
  }
}

[code_blue]

Thanks a lot. this works. However it seems this doesn't work from Kibana when I am doing Sub Series Aggregation. Any idea what I might have to change as I want to make it work from Kibana as well.

[abdon (Abdon Pijpelink)]

Yeah, Kibana has very limited support for nested aggregations. You may want to take a look at Vega visualizations. Those allow you to visualize whatever Elasticsearch returns, including the response of a nested aggregation. There is however a bit of a learning curve to working with Vega.