# Query score based on aggregated values **URL:** \ **Category:** Elasticsearch\ **Created:** [January 16, 2014, 11:43pm UTC](https://discuss.elastic.co/t/query-score-based-on-aggregated-values/15283 "2014-01-16T23:43:51Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Kevin\_Pearson](https://avatars.discourse-cdn.com/v4/letter/k/ac91a4/32.png) [@Kevin\_Pearson](https://discuss.elastic.co/u/Kevin_Pearson)\ **Post date:** [January 16, 2014, 11:43pm UTC](https://discuss.elastic.co/t/query-score-based-on-aggregated-values/15283/1 "2014-01-16T23:43:51Z")
I am wondering if there is a way to use aggregated values inside a query. Example: Say our data contains items and their price: { "id" : string "name" : string "price" : float } I want to do a query that returns the top items that have a price far from the average price of items with the same name. Example Data: _ID | Name | Price_ 1 | Chair | 5.99 2 | Chair | 5.99 3 | Chair | 59.99 4 | Desk | 61.00 5 | Desk | 60.00 6 | Desk | 59.99 The top response would be ID 3, since 59.99 is way higher than the average price for a chair. I believe I need to write a custom score script, but I am not sure how I can get a reference to the average of items with the same name. Thank you, Kevin -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [January 20, 2014, 9:19am UTC](https://discuss.elastic.co/t/query-score-based-on-aggregated-values/15283/2 "2014-01-20T09:19:08Z")
Hey, you could execute a query, sorted by price and in the same request execute a statistical facet for the price field and then check in your client for each hit being returned, if it is above the average value returned by the statistical facet. You could also do this in two roundtrips, getting the statistical average from the facet first and then executing a second query filtering only for products with a price higher than the average. --Alex On Fri, Jan 17, 2014 at 12:43 AM, Kevin Pearson [kevin.pearson.x@gmail.com](mailto:kevin.pearson.x@gmail.com)wrote: > I am wondering if there is a way to use aggregated values inside a query. > > Example: > Say our data contains items and their price: > { > "id" : string > "name" : string > "price" : float > } > > I want to do a query that returns the top items that have a price far from > the average price of items with the same name. > > Example Data: > _ID | Name | Price_ > 1 | Chair | 5.99 > 2 | Chair | 5.99 > 3 | Chair | 59.99 > 4 | Desk | 61.00 > 5 | Desk | 60.00 > 6 | Desk | 59.99 > > The top response would be ID 3, since 59.99 is way higher than the average > price for a chair. > > I believe I need to write a custom score script, but I am not sure how I > can get a reference to the average of items with the same name. > > Thank you, > Kevin > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com) > . > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAGCwEM8aj-g0sbxSTtbueAh8rG7FgaGOzovVEuK7DwLbzwXjbg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGCwEM8aj-g0sbxSTtbueAh8rG7FgaGOzovVEuK7DwLbzwXjbg%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Kevin\_Pearson](https://avatars.discourse-cdn.com/v4/letter/k/ac91a4/32.png) [@Kevin\_Pearson](https://discuss.elastic.co/u/Kevin_Pearson)\ **Post date:** [January 22, 2014, 4:42pm UTC](https://discuss.elastic.co/t/query-score-based-on-aggregated-values/15283/3 "2014-01-22T16:42:22Z")
Thank you Alexander. The first possibility is what we ended up doing for now. We plan on looking into the aggregations framework as a replacement once we upgrade to elasticsearch 1.0 so we don't need to return thousands of documents to the client. Thank you. On Monday, January 20, 2014 1:19:08 AM UTC-8, Alexander Reelsen wrote: > Hey, > > you could execute a query, sorted by price and in the same request execute > a statistical facet for the price field and then check in your client for > each hit being returned, if it is above the average value returned by the > statistical facet. > > You could also do this in two roundtrips, getting the statistical average > from the facet first and then executing a second query filtering only for > products with a price higher than the average. > > --Alex > > On Fri, Jan 17, 2014 at 12:43 AM, Kevin Pearson \<[kevin.p...@gmail.com](mailto:kevin.p...@gmail.com)\ > > > wrote: > > > I am wondering if there is a way to use aggregated values inside a query. > > > > Example: > > Say our data contains items and their price: > > { > > "id" : string > > "name" : string > > "price" : float > > } > > > > I want to do a query that returns the top items that have a price far > > from the average price of items with the same name. > > > > Example Data: > > _ID | Name | Price_ > > 1 | Chair | 5.99 > > 2 | Chair | 5.99 > > 3 | Chair | 59.99 > > 4 | Desk | 61.00 > > 5 | Desk | 60.00 > > 6 | Desk | 59.99 > > > > The top response would be ID 3, since 59.99 is way higher than the > > average price for a chair. > > > > I believe I need to write a custom score script, but I am not sure how I > > can get a reference to the average of items with the same name. > > > > Thank you, > > Kevin > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com) > > . > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/769a6c3d-749d-4311-904d-7f51a37f648d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/769a6c3d-749d-4311-904d-7f51a37f648d%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:55am UTC](https://discuss.elastic.co/t/query-score-based-on-aggregated-values/15283/4 "2017-07-06T01:55:20Z")