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

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.

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:

{"id": 1, "date": "2019-02-01", "amount": 1000}
{"id": 2, "date": "2019-03-01", "amount": 2000}
{"id": 3, "date": "2019-04-01", "amount": 3000}
{"id": 4, "date": "2019-05-17", "amount": 1500}
{"id": 5, "date": "2019-05-17", "amount": 4000}
{"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

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

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?

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

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