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

Question

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.

Bhavya · Accepted Answer · 2022-06-29 09:18:27Z

2

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

answered Jun 29, 2022 at 9:18

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Samball Over a year ago

It returns with a doc_count of 286 but doc_count_error_upper_bound and sum_other_doc_count are 0. Also the buckets array is empty.

Bhavya Over a year ago

@Samball can you share your index mapping ?

Samball Over a year ago

No sorry my mistake made a typo, it works thank you!

Collectives™ on Stack Overflow

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

What I have tried

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I have tried

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related