# How to perform aggregation on nested of nested of nested field?

**URL:** <https://discuss.elastic.co/t/how-to-perform-aggregation-on-nested-of-nested-of-nested-field/324843>\
**Category:** Elasticsearch\
**Created:** [February 7, 2023, 1:52am UTC](https://discuss.elastic.co/t/how-to-perform-aggregation-on-nested-of-nested-of-nested-field/324843 "2023-02-07T01:52:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\
**Post date:** [February 7, 2023, 1:52am UTC](https://discuss.elastic.co/t/how-to-perform-aggregation-on-nested-of-nested-of-nested-field/324843/1 "2023-02-07T01:52:34Z")

</div>

I saw basic examples of how to do a nested aggregation on 1 tiered level of a nested field. But I'm not sure how to perform aggregation when there is more than 1 level of nesting.

I tried the following which sets up 2 levels of nesting and inserts 2 documents:

```auto
PUT order

POST order/_mapping
{
  "properties": {
    "order_items": {
      "type": "nested",
      "properties": {
        "product_id": {
          "type": "long"
        },
        "product": {
          "type": "nested",
          "properties": {
            "name": {
              "type": "keyword"
            },
            "product_id": {
              "type": "long"
            },
            "price": {
              "type": "long"
            }
          }
        }
      }
    },
    "customer": {
      "type": "nested",
      "properties": {
        "state": {
          "type": "keyword"
        },
        "geo": {
          "type": "nested",
          "properties": {
            "point": {
              "type": "geo_point"
            }
          }
        }
      }
    }
  }
}

POST order/_bulk
{"index":{}}
{"order_items":[{"product":{"name":"book","price":10}},{"product":{"name":"pencil","price":1}}],"customer":{"state":"NY"}}
{"index":{}}
{"order_items":[{"product":{"name":"pen","price":5}},{"product":{"name":"eraser","price":1}}],"customer":{"state":"NY"}}

```

I want to get the average price of each product. I tried these two different queries but they both returned empty result sets:

```auto
// ATTEMPT 1
GET order/_search
{
  "size": 0,
  "aggs": {
    "order": {
      "nested": {
        "path": "order_items"
      },
      "aggs": {
        "order_items": {
          "nested": {
            "path": "product"
          },
          "aggs": {
            "product_name" : {
              "terms": {
                "field": "product.name"
              },
              "aggs": {
                "avg_price": {
                  "avg": {
                    "field": "product.price"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

And

```auto
// ATTEMPT 2
GET order/_search
{
  "size": 0,
  "aggs": {
    "order": {
      "nested": {
        "path": "order_items.product"
      },
      "aggs": {
        "product_name" : {
          "terms": {
            "field": "order_items.product.name.keyword"
          },
          "aggs": {
            "avg_price": {
              "avg": {
                "field": "order_items.product.price"
              }
            }
          }
        }
      }
    }
  }
}

```

Can someone tell me what I did wrong?

Also, did I specify my mapping correctly? Do I need to **recursively** specify `type: nested` with each sub-nested object?

---

<div class="post-metadata">

**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\
**Post date:** [February 7, 2023, 2:05am UTC](https://discuss.elastic.co/t/how-to-perform-aggregation-on-nested-of-nested-of-nested-field/324843/2 "2023-02-07T02:05:40Z")

</div>

Oh wait, I found the typing mistake. This query worked:

```auto
GET order/_search
{
  "size": 0,
  "aggs": {
    "order": {
      "nested": {
        "path": "order_items.product"
      },
      "aggs": {
        "product_name" : {
          "terms": {
            "field": "order_items.product.name"
          },
          "aggs": {
            "avg_price": {
              "avg": {
                "field": "order_items.product.price"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 7, 2023, 2:06am UTC](https://discuss.elastic.co/t/how-to-perform-aggregation-on-nested-of-nested-of-nested-field/324843/3 "2023-03-07T02:06:31Z")

</div>

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