Aggregate with bucket selector and sorting on data stream time series data

Elastic Stack Elasticsearch
, ,
1

I have a data stream time series setup where i ingest metrics for different units. These metric was before in a postgres database, but now i try to move it into elasticsearch. One of my usecases is to answer questions like, in the last x minutes what units have generated between "a" and "b" of a sum on a specific metric and then give me the top y units desc.

In sql the query is simple as:

SELECT id, sum(metric)
FROM my_metrics_table
GROUP BY id
HAVING sum(metric) BETWEEN 2 AND 4
ORDER BY 2 ASC
LIMIT 2

I have tried to translate it into an elasticsearch query, but i struggle in the order and limit part. I have a query where i filter on range, aggregate by id, and then uses a bucket_selector to filter on the aggregated value. However, i am not able to add in the sorting and limit correctly.

My current query looks like this:

  "aggs": {
    "group_by_id": {
      "terms": {
        "field": "unit_id",
        "size": 100000000
      },
      "aggs": {
        "agg_metric": {
          "sum": {
            "field": "metric"
          }
        },
        "agg_selector": {
          "bucket_selector": {
            "bucket_path": { "tmppath": "agg_metric" },
            "script": {
              "params": {
                "myMin": 2,
                "myMax": 4
              }
            },
            "lang": "painless",
            "source": "params.tmppath => params.myMin && params.tmppath <= params.myMax"
          }
        }
      }
    }
  },
  "query": {
	"range": {
	  "@timestamp": {
	    "gte": "now-60m"
	  }
	}
  }
}

And i have tried to add in a bucket_sort but that don't work as expected when e.g. limiting to units that only generate zero metrics.

"agg_sort":
  "bucket_sort": {
	"size": 2,
	"sort": [ { "agg_metric": { "order": "asc" } } ]
  }
}

What am i doing wrong :slight_smile: thanks.