Grouping Term Sub Aggregation

Elastic Stack Elasticsearch
1

I'm trying to group term aggregations.

My _search payload:

{
  "size": 25,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "*"
          }
        },
        {
          "terms": {
            "activityId": [1,2]
          }
        }
      ]
    }
  },
  "aggregations": {
    "site_filter": {
      "terms": {
        "field": "definition.indexGroups.indexGroup.groupId"
      },
      "aggregations": {
        "site_keyword": {
          "terms": {
            "field": "definition.indexGroups.indexGroup.index.keyword"
          }
        }
      }
    }
  }
}

Actual Response:

{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 2,
    "max_score" : 2.0,
    "hits" : [
      {
        "_index" : "activity_2",
        "_type" : "activity",
        "_id" : "record_1",
        "_score" : 2.0,
        "_source" : {
          "definition" : {
            "indexGroups" : {
              "indexGroup" : [
                {
                  "index" : [
                    {
                      "code" : "Diabetes - General",
                      "keyword" : "Diabetes - General"
                    },
                    {
                      "code" : "Family Medicine",
                      "keyword" : "Family Medicine"
                    }
                  ],
                  "groupId" : "Topics Group"
                },
                {
                  "index" : [
                    {
                      "code" : "Kidney Disease",
                      "keyword" : "Kidney Disease"
                    }
                  ],
                  "groupId" : "General Group"
                }
              ]
            },
          },
          "activityId" : "1",
          "activityGuid" : "record_1"
        }
      },
      {
        "_index" : "activity_2",
        "_type" : "activity",
        "_id" : "record_2",
        "_score" : 2.0,
        "_source" : {
          "definition" : {
            "indexGroups" : {
              "indexGroup" : [
                {
                  "index" : [
                    {
                      "code" : "Diabetes - General",
                      "keyword" : "Diabetes - General"
                    }
                  ],
                  "groupId" : "Topics Group"
                }
              ]
            },
          },
          "activityId" : "2",
          "activityGuid" : "record_2"
        }
      }
    ]
  },
  "aggregations" : {
    "site_filter" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Topics Group",
          "doc_count" : 2,
          "site_keyword" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Diabetes - General",
                "doc_count" : 2
              },
              {
                "key" : "Family Medicine",
                "doc_count" : 1
              },
              {
                "key" : "Kidney Disease",
                "doc_count" : 1
              }
            ]
          }
        },
        {
          "key" : "General Group",
          "doc_count" : 1,
          "site_keyword" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Diabetes - General",
                "doc_count" : 1
              },
              {
                "key" : "Family Medicine",
                "doc_count" : 1
              },
              {
                "key" : "Kidney Disease",
                "doc_count" : 1
              }
            ]
          }
        }
      ]
    }
  }
}

I expected and wanted the aggregations result to be as follows:

Topics Group:
    Diabetes - General (2)
    Family Medicine (1)
General Group:
    Kidney Disease (1)

However the sub aggregate topics are the same for both top aggregates.

2

You have not provided the mapping for your index but I assume indexGroups and index are of type nested, like this:

{
	"mappings": {
		"activity": {
			"properties": {
				"definition": {
					"properties": {
						"indexGroups": {
							"properties": {
								"indexGroup": {
									"type": "nested",
									"properties": {
										"index": {
											"type": "nested",
											"properties": {
												"code": {
													"type": "keyword"
												},
												"keyword": {
													"type": "keyword"
												}
											}

										},
										"groupId": {
											"type": "keyword"
										}
									}
								}
							}
						}
					}
				}
			}
		}
	}
}

You need to use a nested aggregation:

"aggs": {
		"site_filter": {
			"nested": {
				"path": "definition.indexGroups.indexGroup"
			},
			"aggs": {
				"definition.indexGroups.indexGroup": {
					"terms": {
						"field": "definition.indexGroups.indexGroup.groupId"
					},
					"aggs": {
						"site_keyword": {
							"nested": {
								"path": "definition.indexGroups.indexGroup.index"
							},
							"aggs": {
								"definition.indexGroups.indexGroup.index": {
									"terms": {
										"field": "definition.indexGroups.indexGroup.index.keyword"
									}
								}
							}
						}
					}
				}
			}
		}
	}

Output:

"aggregations": {
    "site_filter": {
      "doc_count": 3,
      "definition.indexGroups.indexGroup": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "Topics Group",
            "doc_count": 2,
            "site_keyword": {
              "doc_count": 3,
              "definition.indexGroups.indexGroup.index": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "Diabetes - General",
                    "doc_count": 2
                  },
                  {
                    "key": "Family Medicine",
                    "doc_count": 1
                  }
                ]
              }
            }
          },
          {
            "key": "General Group",
            "doc_count": 1,
            "site_keyword": {
              "doc_count": 1,
              "definition.indexGroups.indexGroup.index": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "Kidney Disease",
                    "doc_count": 1
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
3

Thank you Arvind!

Your intuition on the mapping was correct. I did not have the mapping defined as nested. As you suggested, I changed the mapping type to "nested" and the aggregation to "nested" and that provided the results we are looking for.

4

You're welcome! For the benefit of the community, please mark the question as solved.

5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.