ElasticSearch aggregation return entire sub object, not just the key

Question

newbies with ElasticSearch we have docs indexed with following structure:

{
    "Id": 1246761,
    "ContentTypeName": "Official Statement",
    "Title": "Official statement Title",    
    "Categories": [
        {
            "Id": 3,
            "Type": 1,
            "Name": "Category A",
            "ParentId": 0
        },
        {
            "Id": 10,
            "Type": 3,
            "Name": "Category B",
            "ParentId": 0
        },
        {
            "Id": 426,
            "Type": 7,
            "Name": "Category C",
            "ParentId": 0
        }
    ]
}

The requirement is to get the aggregated list of categories + document count matching a keyword search. So far our query looks like this:

GET _search
{
  "query": {
    "match_all": {}
  },
  "size": 0,
  "aggs": {
    "my-agg-name": {
      "terms": {
        "field": "Categories.Id"
      }
    }
  }
}

Result is

{
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "my-agg-name" : {
      "doc_count_error_upper_bound" : 23845,
      "sum_other_doc_count" : 1068245,
      "buckets" : [
        {
          "key" : 426,
          "doc_count" : 112651
        },
        {
          "key" : 10,
          "doc_count" : 91146
        },
        
        ....
      ]
    }
  }
}

Is there a way to get back the entire Category object, not only the Id ? Or serialize the category object into string as the key ?

question from:https://stackoverflow.com/questions/65847850/elasticsearch-aggregation-return-entire-sub-object-not-just-the-key

Answer 1

You need to use nested aggregation to achieve your required use case

Adding a working example with index mapping, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "Categories": {
        "type": "nested"
      }
    }
  }
}

Search Query:

{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "resellers": {
      "nested": {
        "path": "Categories"
      },
      "aggs": {
        "my-agg-name": {
          "terms": {
            "field": "Categories.Id"
          },
          "aggs": {
            "categories-doc": {
              "top_hits": {
                "_source": {
                  "includes": [
                    "Categories.Id",
                    "Categories.Type",
                    "Categories.Name",
                    "Categories.ParentId"
                  ]
                },
                "size": 1
              }
            }
          }
        }
      }
    }
  }
}

Search Result:

"aggregations": {
    "resellers": {
      "doc_count": 3,
      "my-agg-name": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": 3,                    // note this
            "doc_count": 1,
            "categories-doc": {
              "hits": {
                "total": {
                  "value": 1,
                  "relation": "eq"
                },
                "max_score": 1.0,
                "hits": [
                  {
                    "_index": "65847850",
                    "_type": "_doc",
                    "_id": "1",
                    "_nested": {
                      "field": "Categories",
                      "offset": 0
                    },
                    "_score": 1.0,
                    "_source": {
                      "ParentId": 0,
                      "Type": 1,
                      "Id": 3,                     // note this
                      "Name": "Category A"
                    }
                  }
                ]

              }
            }
          },
          {
            "key": 10,
            "doc_count": 1,
            "categories-doc": {
              "hits": {
                "total": {
                  "value": 1,
                  "relation": "eq"
                },
                "max_score": 1.0,
                "hits": [
                  {
                    "_index": "65847850",
                    "_type": "_doc",
                    "_id": "1",
                    "_nested": {
                      "field": "Categories",
                      "offset": 1
                    },
                    "_score": 1.0,
                    "_source": {
                      "ParentId": 0,
                      "Type": 3,
                      "Id": 10,
                      "Name": "Category B"
                    }
                  }
                ]
              }
            }
          },
          {
            "key": 426,
            "doc_count": 1,
            "categories-doc": {
              "hits": {
                "total": {
                  "value": 1,
                  "relation": "eq"
                },
                "max_score": 1.0,
                "hits": [
                  {
                    "_index": "65847850",
                    "_type": "_doc",
                    "_id": "1",
                    "_nested": {
                      "field": "Categories",
                      "offset": 2
                    },
                    "_score": 1.0,
                    "_source": {
                      "ParentId": 0,
                      "Type": 7,
                      "Id": 426,
                      "Name": "Category C"
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }