# Nested aggregation with sub-aggregation on parent fields

**URL:** <https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694>\
**Category:** Elasticsearch\
**Created:** [September 15, 2018, 5:30am UTC](https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694 "2018-09-15T05:30:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![krishnaps](https://avatars.discourse-cdn.com/v4/letter/k/bcef8e/32.png) [@krishnaps](https://discuss.elastic.co/u/krishnaps)\
**Post date:** [September 15, 2018, 5:30am UTC](https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694/1 "2018-09-15T05:30:18Z")

</div>

I have following mappings

```
PUT prod_nested
{
  "mappings": {
    "default": {
      "properties": {
        "pkey": {
          "type": "keyword"
        },
        "original_price": {
          "type": "float"
        },
        "tags": {
          "type": "nested",
          "properties": {
            "category": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 30
                }
              }
            },
            "attribute": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 30
                }
              }
            },
            "original_price": {
              "type": "float"
            }
          }
        }
      }
    }
  } 
}

```

I am trying to do aggregation similar to following sql query

```
select tag_attribute,
       tag_category,
       avg(original_price)
FROM products
GROUP BY tag_category, tag_attribute

```

I am able to get the group by part using nested aggregation, but not able to get avg(original\_price).

```
GET prod_nested/_search?size=0
{
    "aggs": {
        "tags": {
          "nested": {
            "path": "tags"
          },
          "aggs": {
            "categories": {
              "terms": {
                "field": "tags.category.keyword",
                "size": 30
              },
              "aggs": {
                "attributes": {
                  "terms": {
                    "field": "tags.attribute.keyword",
                    "size": 30
                  }
                }
              }
            }
          }
        }
      }
}

```

Thanks, in advance.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [September 18, 2018, 7:10am UTC](https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694/2 "2018-09-18T07:10:24Z")

</div>

Slight unrelated, but may I suggest the ES SQL `translate` API to give you a hint about such a query would look like, given an SQL query?  
The API will give you an Elasticsearch query for a certain SQL query, of course within some limits since we do not support all the features in SQL.

[https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-translate.html](https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-translate.html)

---

<div class="post-metadata">

**Author:** ![krishnaps](https://avatars.discourse-cdn.com/v4/letter/k/bcef8e/32.png) [@krishnaps](https://discuss.elastic.co/u/krishnaps)\
**Post date:** [September 19, 2018, 1:57am UTC](https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694/3 "2018-09-19T01:57:53Z")

</div>

This seems interesting, I will definitely try it. However, my current ES version in 6.2.4. It seems elasticseach-sql is available only with version 6.3 and above. Can I get it working with x-pack in earlier versions?

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [September 19, 2018, 4:14am UTC](https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694/4 "2018-09-19T04:14:54Z")

</div>

You could set up a test node on your laptop/desktop, create some mock empty indices but using the mappings of your real indices, and try the `translate` API on the latest ES version. After all, you are looking for an ES query given an SQL query, it's not like you are replacing your production nodes' versions with the latest and greatest ES version.

---

<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:** [October 17, 2018, 4:14am UTC](https://discuss.elastic.co/t/nested-aggregation-with-sub-aggregation-on-parent-fields/148694/5 "2018-10-17T04:14:58Z")

</div>

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