Elasticsearch get sum of values from a field based on another field

Using Elasticsearch i’m trying to get the sum of values from a field based on another field.

I have the following data:

{
    "id": "012345",
    "rows": [
        {
            "id": 0101010,
            "subtotal": 15.55,
            "vat_percentage": 9.0,
            "vat": 1.4
        },
        {
            "id": 0101011,
            "subtotal": 17.0,
            "vat_percentage": 9.0,
            "vat": 1.53
        },
        {
            "id": 0101012,
            "subtotal": 12.98,
            "vat_percentage": 12.0,
            "vat": 1.56
        }
    ]
},
{
    ...
}

What I want is to get the sum of the vat values based on the vat_percentage, like this:

[
    {
        "vat_percentage": "9.0",
        "vat_sum": 2.93
    },
    {
        "vat_percentage": "12.0",
        "vat_sum": 1.56
    }
]

What I have tried

I have tried to make a value_count aggregation but it returns either an incorrect value or a null value:

{
  "aggs": {
    "vat_count": {
      "value_count": {
        "field": "admin_id"
      }
    }
  }
}

Also I tried to use nested queries and aggregations but could not wrap my head around it. This is what I tried:

{
  "aggs": {
    "total_vat": {
      "nested": {
        "path": "rows",
        "query": {
          "term": {
            "rows.vat": "9.0"
          }
        }
      }
    }
  }
}

This throws the following error: Unexpected token START_OBJECT in [total_vat].

How can I achieve this? I have very little experience with Elasticsearch so any help is appreciated.

>Solution :

You need to use the combination of nested, terms and sum aggregation.

Try using this query:

{
    "size": 0,
    "aggs": {
        "total_vat": {
            "nested": {
                "path": "rows"
            },
            "aggs": {
                "unique_percentage": {
                    "terms": {
                        "field": "rows.vat_percentage"
                    },
                    "aggs": {
                        "sum_count": {
                            "sum": {
                                "field": "rows.vat"
                            }
                        }
                    }
                }
            }
        }
    }
}