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
Here are the relevant mappings of my index:
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:
[
{
"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.
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.
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.