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
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:
{"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
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
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?
