Search code examples
elasticsearchelkelasticsearch-painless

how to calculate difference between result of multi aggregation on ELK?

I have an Index that docs is:

id:1
type: Deposit
value:12
timestamp:2022.10.09T00.00.00
####
id:2 
type: withdraw
value:15
timestamp:2022.10.9T00.00.00
####
id:3
type: Deposit
value:17
timestamp:2022.10.09T11.00.00
....

So I run multi aggregation such:

"aggs": {
    "s1": {
      "terms": {
        "field": "type",
        "size": 10
      },
      "aggs": {
        "SUM": {
          "sum": {
            "field": "value"
          }
        }
      }
    }

My result is:

"buckets" : [ { "key" : "DEPOSIT", "doc_count" : 9, "SO" : { "value" : 78983 } }, { "key" : "WITHDRAW", "doc_count" : 9, "SO" : { "value" : 777445 } }

But I want to calculate "value of DEPOSIT - value of WITHDRAW". what is this query???

Solution

  • You can use bucket_script aggregation for this. The bucket script aggs will look as follows.

            "diff": {
          "bucket_script": {
            "buckets_path": {
              "my_var1": "s1['field_value']>s2",
              "my_var2": "s1['field_value']>s2"
            },
            "script": "params.my_var1 - params.my_var2"
          }
        }

    I'm sharing the details and solution below.

    POST test_stackoverflow_question/_bulk
{"index":{}}
{"id":"1", "type": "Deposit", "value":12, "timestamp":"2022.10.09T00.00.00"}
{"index":{}}
{"id":"2", "type": "withdraw", "value":15, "timestamp":"2022.10.9T00.00.00"}
{"index":{}}
{"id":"3", "type": "Deposit", "value":17, "timestamp":"2022.10.09T11.00.00"}

    A Sibling pipeline ag has the option to select specific keys from multi-bucket if the terms refer to a multipart aggregation such as agg. For example, a bucket_script can select (via package keys) two custom buckets to perform the calculation:

    GET test_stackoverflow_question/_search
{
  "size": 0,
  "aggs": {
    "calculate_diff": {
      "filters": {
        "filters": {
          "all": {
            "match_all": {}
          }
        }
      },
      "aggs": {
        "s1": {
          "terms": {
            "field": "type.keyword",
            "size": 10
          },
          "aggs": {
            "s2": {
              "sum": {
                "field": "value"
              }
            }
          }
        },
        "diff": {
          "bucket_script": {
            "buckets_path": {
              "my_var1": "s1['Deposit']>s2",
              "my_var2": "s1['withdraw']>s2"
            },
            "script": "params.my_var1 - params.my_var2"
          }
        }
      }
    }
  }
}

    Ref: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html https://www.elastic.co/guide/en/elasticsearch/reference/7.17/search-aggregations-pipeline.html

    solution