Nested aggregation with sub-aggregation on parent fields

[krishnaps (krishna sarthi)]

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.

[Andrei_Stefan]

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

[krishnaps (krishna sarthi)]

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?

[Andrei_Stefan]

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.