Elasticsearch sort, substitute a missing field with another

I have some products I would like to sort by price. They have two properties, product_price and a special_price.

special_price is not always present in the doc. On reading the documentation it looks like you can add a missing element and have it search on this instead.

I’ve added the following to the request:

"sort" : [
        { "special_price" : {"missing" : "product_price"} },
],

The docs state:

The missing parameter specifies how docs which are missing the field should be treated: The missing value can be set to _last, _first, or a custom value (that will be used for missing docs as the sort value).

I’ve also tried specifying the fallback value as

"sort" : [
        { "special_price" : {"missing" : "doc['product_price'].value"} },
],

I can’t see what I’m missing here?

Answer

missing support three values _last, _first or a custom value (a number). You can’t specify other field as value of missing parameter. Since you want to fallback to the value of different field then you can use script based sorting.

So the sort can be defined as:

{
  "sort": {
    "_script": {
      "type": "number",
      "script": {
        "lang": "painless",
        "source": "if(doc['special_price'].size() != 0) { return doc['special_price'].value; } return doc['product_price'].value;"
      },
      "order": "desc"
    }
  }
}