# How to get documents in Elasticsearch based on aggregation output values?

**URL:** <https://discuss.elastic.co/t/how-to-get-documents-in-elasticsearch-based-on-aggregation-output-values/182109>\
**Category:** Elasticsearch\
**Created:** [May 22, 2019, 12:50am UTC](https://discuss.elastic.co/t/how-to-get-documents-in-elasticsearch-based-on-aggregation-output-values/182109 "2019-05-22T00:50:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Thomas\_Lee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thomas_lee/32/46583_2.png) [@Thomas\_Lee](https://discuss.elastic.co/u/Thomas_Lee)\
**Post date:** [May 22, 2019, 12:50am UTC](https://discuss.elastic.co/t/how-to-get-documents-in-elasticsearch-based-on-aggregation-output-values/182109/1 "2019-05-22T00:50:06Z")

</div>

I'd like to use aggregation outputs as an input to filtering for documents in one query.

For example, I'd like to get sales documents in the last 24 hours where sale amount is greater than the average of sale amounts in the last 3 months before the current month (e.g. Feb-Apr if we're in May). The average sales amount would be an aggregation.

Tried using script fields because it filters on docs, but not sure how to access aggregation results from script. [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-script-fields.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-script-fields.html)

Another thought is to use a 3 months date rangeQuery at the top, then have a 24 hour date histogram with a top hits aggregation nested underneath. However, I would need some sort of scripted filter to filter out documents based on the avg sales aggregation.

Sample sales documents you can import via a POST of the contents below to [Bulk API](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-bulk.html):

```auto
{"index":{}}
{"id": 1, "date": "2019-02-01", "amount": 1000}
{"index":{}}
{"id": 2, "date": "2019-03-01", "amount": 2000}
{"index":{}}
{"id": 3, "date": "2019-04-01", "amount": 3000}
{"index":{}}
{"id": 4, "date": "2019-05-17", "amount": 1500}
{"index":{}}
{"id": 5, "date": "2019-05-17", "amount": 4000}
{"index":{}}
{"id": 6, "date": "2019-05-17", "amount": 8000}

```

Based on the documents above, the average of last 3M before this month (May) is (1000 + 2000 + 3000) / 3 = 2000. Documents in the last 24 hours that have amounts \> 2000 are just id 5, id 6.

In SQL, the query would look like

```auto
SELECT * 
FROM sales 
WHERE `date` >= '2019-05-17' 
       AND amount > (SELECT AVG(amount) 
                     FROM sales 
                     WHERE `date` BETWEEN '2019-02-01' AND '2019-04-30'); 

```

and return

```auto
id	date	amount
5	2019-05-17	4000
6	2019-05-17	8000

```

How do I achieve the same with Elasticsearch in one query/request?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [May 23, 2019, 10:29pm UTC](https://discuss.elastic.co/t/how-to-get-documents-in-elasticsearch-based-on-aggregation-output-values/182109/2 "2019-05-23T22:29:08Z")

</div>

> [@Thomas\_Lee](#):
>
> How do I achieve the same with Elasticsearch in one query/request?

You can't at the moment sorry! ☹  
You will need to run the agg to get the average, then run a separate query to get the docs that match the values.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 20, 2019, 10:29pm UTC](https://discuss.elastic.co/t/how-to-get-documents-in-elasticsearch-based-on-aggregation-output-values/182109/3 "2019-06-20T22:29:13Z")

</div>

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