Nested aggregation in nested field?

Question

I am new to elasticsearch and don't know a lot about aggregations but I have this ES6 mapping:

{
    "mappings": {
        "test": {
            "properties": {
                "id": {
                    "type": "integer"
                }
                "countries": {
                    "type": "nested",
                    "properties": {
                        "global_id": {
                            "type": "keyword"
                        },
                        "name": {
                            "type": "text",
                            "fields": {
                                "raw": {
                                    "type": "keyword"
                                }
                            }
                        }
                    }
                },
                "areas": {
                    "type": "nested",
                    "properties": {
                        "global_id": {
                            "type": "keyword"
                        },
                        "name": {
                            "type": "text",
                            "fields": {
                                "raw": {
                                    "type": "keyword"
                                }
                            }
                        },
                        "parent_global_id": {
                            "type": "keyword"
                        }
                    }
                }
            }
        }
    }
}

How can I get all documents grouped by areas which is then grouped by countries. Also the document has to be returned in full, not just the nested document. Is this even possible ?

Answer 1

1) Aggregation _search query:

first agg by area, with the path as this is nested. Then reverse to the root document and nested agg to country.

{
  "size": 0,
  "aggs": {
    "agg_areas": {
      "nested": {
        "path": "areas"
      },
      "aggs": {
        "areas_name": {
          "terms": {
            "field": "areas.name"
          },
          "aggs": {
            "agg_reverse": {
              "reverse_nested": {},
              "aggs": {
                "agg_countries": {
                  "nested": {
                    "path": "countries"
                  },
                  "aggs": {
                    "countries_name": {
                      "terms": {
                        "field": "countries.name"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

2) retrieve documents:

add a tophits inside your aggregation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html

top_hits is slow so you will have to read documentation and adjust size and sort to your context.

...
"terms": {
            "field": "areas.name"
          },
    "aggregations": {
                    "hits": {
                        "top_hits": { "size": 100}
                    }
                },
    ...