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

[sleshJdev]

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 a related topic on Kibana forum

ELK stack version is 7.0

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[elasticmachine]

Pinging @elastic/kibana-gis (Team:Geo)

[sleshJdev]

Guys, how you feel about introducing sql-having-like functionality into Maps? Currently, it only supports filtering by filter from a document, but would be useful to be able to filter out by aggregated properties.

[sleshJdev]

I could try to prepare PR for that

[nreese]

how you feel about introducing sql-having-like functionality into Maps?

What are you proposing? Any SQL functionality would have to use elastic SQL plugin. Here is a link to an open issue for supporting SQL in Maps if you would like to describe your use case there.

[sleshJdev]

As I described in the issue intro, I need some wat to do additional filtering for aggregated data when I configure Map. For example, I want to filter out metric value by some criteria(how sql-having behaves). Now I can only filter the documents, but not aggregated data.

[sleshJdev]

Does it make sense?

[nreese]

Would exposing https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html resolve the issue?

[sleshJdev]

Hmm, it's a map-reduce approach and it could potentially work for sure. But it could involve too many changes to re-work existing code to work with a new API. What about search-aggregations-pipeline-bucket-selector-aggregation? It seems to be much easier for integration. What do you think?

[nreese]

That would be useful to expose as well.

[sleshJdev]

Cool, I'll try to prepare PR. No ETA, sorry

[thomasneirynck]

related #55520

[thomasneirynck]

Most map-related enhancement will go to the Maps app, and it is unlikely any improvements will be added to the region-map visualization. This is a useful ER though. To improve visibility, I modified the title of this ticket to reflect that this should target the Maps-app.

[odmby]

this is useful not only for maps, but for any visualization to have "post result" filtering on the calculated values. ("HAVING" in SQL)

[wylieconlon]

It seems like most users who are requesting this feature are asking for filtering based on the aggregated number, so I've proposed a few simplifications to Lens. We haven't yet discussed this, but you may find it useful in thinking about the problem: #86190

[elasticmachine]

Pinging @elastic/kibana-presentation (Team:Presentation)

[nreese]

Closed by #154983