# Max\_score anyone? **URL:** \ **Category:** Elasticsearch\ **Created:** [March 12, 2014, 8:21am UTC](https://discuss.elastic.co/t/max-score-anyone/16313 "2014-03-12T08:21:17Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![george\_monroe](https://avatars.discourse-cdn.com/v4/letter/g/bbce88/32.png) [@george\_monroe](https://discuss.elastic.co/u/george_monroe)\ **Post date:** [March 12, 2014, 8:21am UTC](https://discuss.elastic.co/t/max-score-anyone/16313/1 "2014-03-12T08:21:17Z")
Hi guys, I've been beating my head against this one for a while. Any help is really appreciated. I have a Parent / Child relationship that is 1 to Many - each parent has lots of children. My use case is to only return those parents whose children aggregate (sum to) a certain $ amount. But with the added caveat: the $ amount has to be within a certain range. How best to do this? Imposed requirements: - return all such parents in one go (one request) - return only those parents who have children whose sum/aggregate falls within a range (between MIN and MAX) The new aggregations framework seems to be off limits since it does not work across Parent/Child. The post\_filter seems to be off limits since it does not work within has\_child. Which attack line to take? Here is my current query that satisfies only one half of the requirement: the min\_score curl -X GET '0:9200/segmentation-cd/animal/\_search?pretty' -d '{ "min\_score" : 250, "query" : { "bool" : { "must" : [ { "function\_score" : { "query" : { "term" : { "animal.sex" : "neutered" }}, "script\_score" : {"script" : "0"}}}, { "has\_child" : { "type" : "customer", "score\_type" : "sum", "query" : { "function\_score" : { "filter" : { "query" : { "term" : { "customer.first\_name" : "Michael" }}}, "script\_score" : {"script" : "0"}}}}}, { "has\_child" : { "type" : "visit", "score\_type" : "sum", "query" : { "function\_score" : { "filter" : { "bool" : { "must" : [ { "range" : { "visit\_date" : { "from" : "2001-07-28T00:00:00.000Z", "to" : "2011-11-18T00:00:00.000Z", "include\_lower" : false, "include\_upper" : true}}}]}}, "script\_score" : {"script" : "\_source.revenue"}}}}}]}}}' -- 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/9781ee00-f48f-4bf1-b9fd-1e0b1abaea16%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9781ee00-f48f-4bf1-b9fd-1e0b1abaea16%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [March 12, 2014, 11:40am UTC](https://discuss.elastic.co/t/max-score-anyone/16313/2 "2014-03-12T11:40:09Z")
Hi Yuri First, here's a query that will work for you: > I'm using a has\_parent query to sum up all the values of the revenue field, then using a function\_score query to ensure that those values fall within a range. However, your data model feels like you are trying to implement a relational database in Elasticsearch. This is not optimal 🙂 These parent-child joins have a cost. It is much more efficient just to denormalise your data, ie store everything you want to be able to search on in the same document. For instance, your customer's name isn't likely to change. In fact the owner of the animal isn't likely to change either. So why not store the customer info in the animal document? Keeping visits as a child may be the correct approach, but you could also look at storing them as nested objects: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. It depends whether you want to be able to return individual visits as search results (keep them as parent-child) or not (use nested). hth Clint On 12 March 2014 09:21, Yuri Panchenko [yuri.panchenko@gmail.com](mailto:yuri.panchenko@gmail.com) wrote: > Hi guys, > > I've been beating my head against this one for a while. Any help is really > appreciated. I have a Parent / Child relationship that is 1 to Many - each > parent has lots of children. My use case is to only return those parents > whose children aggregate (sum to) a certain $ amount. But with the added > caveat: the $ amount has to be within a certain range. How best to do this? > > Imposed requirements: > > - return all such parents in one go (one request) > - return only those parents who have children whose sum/aggregate falls > within a range (between MIN and MAX) > > The new aggregations framework seems to be off limits since it does not > work across Parent/Child. The post\_filter seems to be off limits since it > does not work within has\_child. Which attack line to take? > > Here is my current query that satisfies only one half of the requirement: > the min\_score > > curl -X GET '0:9200/segmentation-cd/animal/\_search?pretty' -d > '{ "min\_score" : 250, > "query" : { > "bool" : { > "must" : [ > { "function\_score" : { > "query" : { > "term" : { "animal.sex" : "neutered" }}, > "script\_score" : {"script" : "0"}}}, > { "has\_child" : { > "type" : "customer", > "score\_type" : "sum", > "query" : { > "function\_score" : { > "filter" : { > "query" : { > "term" : { "customer.first\_name" : > "Michael" }}}, > "script\_score" : {"script" : "0"}}}}}, > { "has\_child" : { > "type" : "visit", > "score\_type" : "sum", > "query" : { > "function\_score" : { > "filter" : { > "bool" : { > "must" : [ > { "range" : { "visit\_date" : { > "from" : "2001-07-28T00:00:00.000Z", "to" : "2011-11-18T00:00:00.000Z", > "include\_lower" : false, "include\_upper" : true}}}]}}, > "script\_score" : {"script" : "\_source.revenue"}}}}}]}}}' > > -- > 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/9781ee00-f48f-4bf1-b9fd-1e0b1abaea16%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9781ee00-f48f-4bf1-b9fd-1e0b1abaea16%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/9781ee00-f48f-4bf1-b9fd-1e0b1abaea16%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/9781ee00-f48f-4bf1-b9fd-1e0b1abaea16%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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/CAPt3XKRigoNJBYk4YD8kX4LPSv0Cg8mygShMrAKoggxREcxYgg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAPt3XKRigoNJBYk4YD8kX4LPSv0Cg8mygShMrAKoggxREcxYgg%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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:43am UTC](https://discuss.elastic.co/t/max-score-anyone/16313/3 "2017-07-06T01:43:51Z")