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

Elastic Stack Elasticsearch
1

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:

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:

// 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

// 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?

2

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

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"
              }
            }
          }
        }
      }
    }
  }
}
3

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