# Sort both hits and inner hits **URL:** \ **Category:** Elasticsearch\ **Tags:** painless\ **Created:** [October 26, 2022, 9:19am UTC](https://discuss.elastic.co/t/sort-both-hits-and-inner-hits/317493 "2022-10-26T09:19:41Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![BrunoSkoon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brunoskoon/32/112530_2.png) [@BrunoSkoon](https://discuss.elastic.co/u/BrunoSkoon)\ **Post date:** [October 26, 2022, 9:19am UTC](https://discuss.elastic.co/t/sort-both-hits-and-inner-hits/317493/1 "2022-10-26T09:19:41Z")
Hello. I'm using Elasticsearch 8.4.3 and I need help to sort both the hits and inner hits. My data set is a list of product types, each containing a list of `products`. Each product then contains a `price` object with fields such as `day_price` and `day_price_week`. My goal is to sort the product types by cheapest product, based on a `number_of_days` parameter, and calculated by a script. So far, I managed to sort the product types, but I don't know to sort the `products` inner hits using the same script, or at least to mention which `product` was the cheapest in the results. I set anonymous public read access for my index, so feel free to test the following examples: [https://skoon-search-poc.es.westeurope.azure.elastic-cloud.com/test05](https://skoon-search-poc.es.westeurope.azure.elastic-cloud.com/test05) Here are the relevant mappings of my index: ```json PUT https://skoon-search-poc.es.westeurope.azure.elastic-cloud.com/test05/_search { "mappings": { "properties": { "products": { "type": "nested", "properties": { "price": { "type": "nested", "properties": { "day_price": { "type": "scaled_float", "scaling_factor": 100 }, "day_price_week": { "type": "scaled_float", "scaling_factor": 100 }, "day_price_four_weeks": { "type": "scaled_float", "scaling_factor": 100 } } } } } } } } ``` Here's an example of my data, 2 product types, each containing 2 products, and they are not already sorted by cheapest product: ```json [ { "id": 1, "product_type_name": "Battery 1", "products": [ { "product_name": "Battery 1 in Eindhoven", "price": { "day_price": 12, "day_price_week": 10, "day_price_four_weeks": 8 } }, { "product_name": "Battery 1 in Delft", "price": { "day_price": 10, "day_price_week": 8, "day_price_four_weeks": 6 } } ] }, { "id": 2, "product_type_name": "Battery 2", "products": [ { "product_name": "Battery 2 in Amsterdam", "price": { "day_price": 8, "day_price_week": 6, "day_price_four_weeks": 4 } }, { "product_name": "Battery 2 in Utrecht", "price": { "day_price": 5, "day_price_week": 4, "day_price_four_weeks": 3 } } ] } ] ``` When sorting by cheapest product, my expectation is that "Battery 2" should appear first, and inside the Battery 2 product type, the product "Battery 2 in Utrecht" should be first, because its `day_price` is cheaper (see the script below). And for "Battery 1", "Battery 1 in Delft" should be first because it's also cheaper than the other one. I have 2 partial solutions, one using `sort`, and another using `_score`. * * * Partial solution 1: I can sort the ProductTypes, but I can't re-sort the inner hits with the same script (it doesn't seem to be supported within the `inner_hits`), and I can't know which Product was the cheapest without reverse-engineering the `sort` value from the results. ```json GET https://skoon-search-poc.es.westeurope.azure.elastic-cloud.com/test05/_search { "query": { "nested": { "path": "products", "query": { "match_all": {} }, "inner_hits": { // TODO: can't sort the inner hits with script } } }, "sort": [ { "_script": { "nested": { "path": "products.price" }, "type": "number", "order": "asc", "script": { "lang": "painless", "source": """ if (params["number_of_days"] >= 28) { return params["number_of_days"] * doc['products.price.day_price_four_weeks'].value; } if (params["number_of_days"] >= 7) { return params["number_of_days"] * doc['products.price.day_price_week'].value; } return params["number_of_days"] * doc['products.price.day_price'].value; """, "params": { "number_of_days": 4 } } } } ] } ``` * * * Partial solution 2: I'm replacing the \_score by my own script calculation, and then trying to sort both the product types and the products by `_score` ascending. The `inner_hits` are sorted properly, but the `sort` value for the product types is an **average** of the scores of the `inner_hits`. I want it to be a **minimum** instead, but `"mode": "min"` is not accepted for sorting by `_score`, even though the code completion suggests it. ```json GET https://skoon-search-poc.es.westeurope.azure.elastic-cloud.com/test05/_search { "query": { "nested": { "path": "products.price", "query": { "function_score": { "script_score": { "script": { "lang": "painless", "source": """ if (params["number_of_days"] >= 28) { return params["number_of_days"] * doc['products.price.day_price_four_weeks'].value; } if (params["number_of_days"] >= 7) { return params["number_of_days"] * doc['products.price.day_price_week'].value; } return params["number_of_days"] * doc['products.price.day_price'].value; """, "params": { "number_of_days": 4 } } }, "boost_mode": "replace" } }, "inner_hits": { "sort": [ { "_score": { "order": "asc" } } ] } } }, "sort": [ { "_score": { "order": "asc" // "mode": "min" TODO: mode doesn't work on _score } } ] } ``` Thank you in advance for your help. ---
**Author:** ![BrunoSkoon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brunoskoon/32/112530_2.png) [@BrunoSkoon](https://discuss.elastic.co/u/BrunoSkoon)\ **Post date:** [October 26, 2022, 11:00am UTC](https://discuss.elastic.co/t/sort-both-hits-and-inner-hits/317493/2 "2022-10-26T11:00:26Z")
I fixed it by adding the `score_mode` field. The auto-completion doesn't include the min value, but the documentation mentions it, and it works: ```json { "query": { "nested": { "path": "products.price", "query": { "function_score": { "script_score": { "script": { "lang": "painless", "source": """ if (params["number_of_days"] >= 28) { return params["days"] * doc['products.price.day_price_four_weeks'].value; } if (params["number_of_days"] >= 7) { return params["days"] * doc['products.price.day_price_week'].value; } return params["number_of_days"] * doc['products.price.day_price'].value; """, "params": { "number_of_days": 4 } } }, "boost_mode": "replace" } }, "inner_hits": { "sort": [ { "_score": { "order": "asc" } } ] }, "score_mode": "min" // <<< this fixed it } }, "sort": [ { "_score": { "order": "asc" } } ] } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 23, 2022, 11:00am UTC](https://discuss.elastic.co/t/sort-both-hits-and-inner-hits/317493/3 "2022-11-23T11:00:58Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.