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

**URL:** <https://discuss.elastic.co/t/aggregate-with-bucket-selector-and-sorting-on-data-stream-time-series-data/359041>\
**Category:** Elasticsearch\
**Tags:** painless, datastreams, aggregations\
**Created:** [May 8, 2024, 9:20am UTC](https://discuss.elastic.co/t/aggregate-with-bucket-selector-and-sorting-on-data-stream-time-series-data/359041 "2024-05-08T09:20:36Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ahw](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@ahw](https://discuss.elastic.co/u/ahw)\
**Post date:** [May 8, 2024, 9:20am UTC](https://discuss.elastic.co/t/aggregate-with-bucket-selector-and-sorting-on-data-stream-time-series-data/359041/1 "2024-05-08T09:20:36Z")

</div>

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:

```auto
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:

```auto
  "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.

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

```

What am i doing wrong 🙂 thanks.