Elasticsearch: sort by field with null values last

Question

I want sort by name, but keep entries with null or missing price data at the end of the results.

I have tried:

sort: {
    sorting.name: {
        order: "asc"
    },
    prices.minprice.price: {
        missing: "_last"
    }
}

but this only sorts by name.

Answer 1

Actually you could use the missing order.

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html#_missing_values

Answer 2

To do this we can use a custom function score (which is faster than custom script based sorting according to elasticsearch documentation). When price is missing, the numeric value will return 0.

We then sort on score first to split into a section with missing price data, and a section without missing price data, and then sort each of these two sections based on name.

{
    "query": {
        "function_score": {
            "boost_mode": "replace",
            "query": {"match_all": {}},
            "script_score": {
                "script": "doc['price'].value == 0 ? 0 : 1"
            }   
        }
    },
    "sort": [
       "_score",
       "name"
    ]
}