Query documents based on aggregation results in single query

OpenSearch
1

The General Idea was already described in the following blog post:

https://discuss.elastic.co/t/how-to-get-documents-in-elasticsearch-based-on-aggregation-output-values/182109

The basic notion here is to do within a single query

  • aggregate
  • get document according to agg results

The need to fulfil this in a single query is similar to the basic capability of the next sql

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');