Hi I would like to understand how deep you can go with Elastic Search queries.

I have an index with a property "Price" and i would extract any document where Price > AVG(Price).

For example if I have 6 documents with this prices:

532,400,299,100,100,33

it should extracts documents 299, 400, 532 because > of price average (244).

I can reach this goal with simple elastic search query or I need to use something else, for example scripting (https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html) or custom script with another language (nodejs, python, .Net etc) or use some ETL tool like Logstash?

I have some difficulties to identify the road. I tried to use subquery in ES using query but it's not supported.

({ "query" : "select * from myIndex where Price > (select avg('Price') from myIndex) "})

CodePudding user response：

I think this query will solve your problem in efficient way:

{
   "aggs":{
      "price_gte_244":{
         "filter":{
            "range":{
               "price":{
                  "gte":244
               }
            }
         },
         "aggs":{
            "avg_price":{
               "avg":{
                  "field":"price"
               }
            }
         }
      }
   }
}

When 244 could be any value/variable that you want

If you want to read more about filter aggregation: Filter aggregation docs

CodePudding user response：

Depending on how your data is structured, you can get the avg value across all of your documents through an AVG aggregation. Then, use that value in a Range query to find the documents greater than this value. You can also look into the Bucket Selector Aggregation which has a lot of similarity with a SQL HAVING clause.