nested in nested aggregation in elasticsearch

Question

how to aggregate a value in nested in nested position in elasticsearch? i have no problem with one nested object but in nest inside nested object i'm confusing...

sample data:

"cat_a": [
      {
        "position": "base",
        "tools": [
          "inside",
          "out"
        ],
        "entry": [
          {
            "tx_a": "inside",
            "rx_a": [
              "soft_1",
              "soft_2",
              "soft_3",
              "soft_4"
            ],
            "number": 0.018
          },
          {
            "tx_a": "out",
            "rx_a": [
              "soft_1",
              "soft_3",
              "soft_5",
              "soft_7"
            ],
            "number": 0.0001
          }
        ],
        "basic": true
      }
    ]

desire result:

{
"aggregations": {
        "sample_agg": {
            "count": {
                "buckets": [
                    {
                        "key": "soft_1",
                        "doc_count": 2
                    },
                    {
                        "key": "soft_3",
                        "doc_count": 2
                    },
                    {
                        "key": "soft_2",
                        "doc_count": 1
                    }
                ]
            }
        }
    }
}

my elasticsearch version is 6.2.3. in index mapping i set type of "cat_a" and "entry" fields to "nested", when i query an aggregate from "tools" field that in root(level 1) of "cat_a" there is no problem and it's working, but in aggregation on rx_a (that's in level 2 ) i can not retrieve result, it's or empty or shown error because of my bad query.

query for level1 agg:

{
    "aggs" : {
        "sample_agg" : {
            "nested" : {
                "path" : "cat_a"
            },
            "aggs" : {
                "count" : { "terms" : { "field" : "cat_a.rx_a.keyword" ,"size":10} }
            }
        }
    }
}

how i do for nested in nested?

Answer 1

I've just constructed and run some "nested in nested" aggregation.
This gives more difficult syntax. I would prefer to have more flat expressions.
IMHO, it could be useful if we need to know doc_count on different levels or if we have a huge amount of nested in nested aggregations.

So, below
root-nested-1 is a "nested in nested" aggregation and
root-nested-2 is the same "flat" aggregation.

    "root-nested-1": {
      "nested": {
        "path": "rootCollection"
      },
      "aggs": {
        "child-nested": {
          "nested": {
            "path": "rootCollection.childCollection"
          },
          "aggs": {
            "TRIAL": {
              "terms": {
                "field": "rootCollection.childCollection.trial",
                "include": [ "true" ]
              }
            }
          }
        }
      }
    },

    "root-nested-2": {
      "aggs": {
        "TRIAL": {
          "terms": {
            "field": "rootCollection.childCollection.trial",
            "include": [ "true" ]
          }
        }
      },
      "nested": {
        "path": "rootCollection.childCollection"
      }
    }

If we compare my real aggregations' results, there is no much difference

    "nested#root-nested-1" : {
      "doc_count" : 31,
      "nested#child-nested" : {
        "doc_count" : 35,
        "lterms#TRIAL" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : 1,
              "key_as_string" : "true",
              "doc_count" : 13
            }
          ]
        }
      }
    }

    "nested#root-nested-2" : {
      "doc_count" : 35,
      "lterms#TRIAL" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : 1,
            "key_as_string" : "true",
            "doc_count" : 13
          }
        ]
      }
    },

Answer 2

Elasticsearch allows multiple levels in nesting. Assuming your mapping has the correct nesting defined, just change path to cat_a.entry and field to cat_a.entry.rx_a.keyword.