Nested Script Sorting - Access all matching nested documents inside script

Elastic Stack Elasticsearch
1

I am trying to find a way to sort root documents, based on the cumulative price of all the matching nested documents.

Each vendor is stored as the root document, with all their products stored as nested documents:

{
    "properties": {
        "vendorId": { "type": "keyword" },
        "products": {
            "type": "nested",
            "properties": {
                "productId": { "type": "keyword" },
                "category": { "type": "text" },
                // ... Truncated
                "price": { "type": "number" }
            }
        },
        // ... Truncated
    }
}

A user can search for vendors containing "the following products", and the query for that I have managed to get working just fine. When the user searches, I make use of inner_hits on the nested queries to return the matching products in order to build up the final result to the user.

The part I am struggling with, is how to sort the vendor results based on the cumulative total of all the matching products (not even sure if it is possible). I managed to get some level of sorting going by using nested script sorting (since the price needs to be calculated based on the quantity they want):

{
    "query": {
        // ... Truncated
    },
    "sort": [
        {
            "_script": {
                "nested": {
                    "path": "products",
                    "filter": [
                        // ... Similar filter to the original query in order to only get matching nested documents
                    ]
                },
                "type": "number",
                "mode": "min",
                "order": "asc",
                "script": {
                    "lang": "painless",
                    "source": "return doc['products.price'].value * params['quantity']",
                    "params": {
                        "quantity": 10
                    }
                }
            }
        }
    ]
}

The issue with this sort script is that it will only sort by the lowest price of any of the matching documents, where I would want to sort by the cumulative price of all the matching products:

{
    // ... Truncated
    "script": {
        "lang": "painless",
        "source": "def totalPrice = 0; for (int i = 0; i < doc['products'].length; i++) { totalPrice += doc['products'].get(i).price * params['quantity']; } return totalPrice;",
        "params": {
            "quantity": 10
        }
    }
}

Any help would be greatly appreciated

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.