Introduce bucket-selector or any post-aggregation filtering(like having in sql) into region map elastic-search/kibana

Elastic Stack Kibana
1

I have tabular data by districts and days. Each row contains diff between the current and previous days, i.e. relative value.

     district |     day    | metric 
    ----------+------------+--------
     D1       | 2020-04-12 | -11     
     D1       | 2020-04-13 |  40
     D2       | 2020-04-13 |  20     
     D1       | 2020-04-14 |  11         
     D1       | 2020-04-15 | -50

I need to visualize this data on Kibana's region map.

So, the metric is grouped by district and summarized. Here is the ElasticSearch query generated by Kibana:

    {"aggs": {
        "2": {
          "terms": {
            "field": "district",
            "size": 300,
            "order": {
              "1": "desc"
            }},
          "aggs": {
            "1": {
              "sum": {
                "field": "metric"
              }}}}},
      "query": {
        "bool": {
          "must": [{
              "range": {
                "@timestamp": {
                  "format": "strict_date_optional_time",
                  "gte": "2020-04-12T00:00:00.0Z",
                  "lte": "2020-04-16T00:00:00.0Z"
                }}}]}}}

Depending on the selected data rage the result will vary. For example, this query selects all data (see @timestamp filter) and metric values for districts D1 and D2 will be -10 and 20. If I'll change the filter to select data for 12-13 April it will be -11 + 40 = 29 for D1 and 20 for D2.

In Kibana I need to filter out buckets with negative sum(metric) value and show districts only with a positive sum value. I couldn't find any working solution. I've tried

  • Kibana's JSON input + Bucket Selector
    Aggregation
    Visual
  • Kibana's Vega Graphs, but it seems that it doesn't support region maps.
    I'd like to avoid it as it's quite complicated.
  • Build a new index based on the existing ones, but it's not possible as
    a result depends on a date range filter, so I cannot pre-calculate metrics and filter out negatives because I don't know what date range is in advanced

Nothing of this worked for me. I was able to compose a working Elastic Search query that does exactly what I want, but I don't know how to visualize it using the region map:

    {"aggs": {
        "2": {
          "terms": {
            "field": "district",
            "size": 300,
            "order": {
              "1": "desc"
            }},
          "aggs": {
            "1": {
              "sum": {
                "field": "metric"
              },
              "1_bucket_selector": { -- here is main part, how get it in region map?
                "bucket_selector": {
                  "buckets_path": {
                    "metricSum": "1"
                  },
                  "script": "params.metricSum > 0"
                }}}}}}}

So, any workaround to achieve what I want?

Here is related topic on SO

2

Someone, please help?

3

Unfortunately you can't create filters based on metrics at the current time, but you might be able to accomplish something using Kibana Canvas using custom expressions. Would you like more info about this?

4

Sure, I'd be appreciate

5

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