elasticsearch - Elastic Search sort preprocessing

Question

I have an index in ES that has, in addition to other fields, revenue_amount and revenue_currency fields. The revenue is stored in different currencies. At run time, all currencies are converted to USD and rendered.

Now, I would like to support sorting on the revenue_amount field. The problem is ES sorts results in terms of revenue prior to converting to USD, and so a revenue returned at the top might not be the highest revenue after converting to USD.

I was wondering, if its possible that before sorting, ES calls a user-defined function that changes a field value, and then apply sort afterwards? Something like this:

revenue_converted = convertToUSD(revenue)

And so the sorting will be applied to revenue_converted, rather than revenue.

I know I can convert the currencies at index time, but that will require refreshing the index every time the rates are updated, and so I would like to avoid it, if possible.

Answer 1

You have two ways of achieving this: one is by using script-based sorting as keety mentioned:

{
    "query" : {
        ....                                    <--- your query goes here
    },
    "sort" : {
        "_script" : {
            "script" : "doc.revenue_amount.value * usd_conversion_rate",
            "type" : "number",
            "params" : {
                "usd_conversion_rate" : 0.4273  <--- the conversion rate to USD
            },
            "order" : "desc"
        }
    }
}

The usd_conversion_rate factor is the conversion rate to USD. So for instance, if 1 USD is worth 2.34 units of another currency, the usd_conversion_rate factor would be 1 / 2.34 (or 0.4273). When multiplied with revenue_amount it'll give you the amount in the USD reference currency.

Script-based sorting is not very performant, though, and the recommendation is to use a function_score so results can be sorted by score instead. That leads us to the second way of achieving what you need and it goes like this. One way is by using a script_score function, but that involves scripting again.

{
  "query": {
    "function_score": {
      "query": {},
      "functions": [
        {
          "script_score": {
            "script": "doc.revenue_amount.value * usd_conversion_rate",
            "boost_mode": "replace",
            "params": {
              "usd_conversion_rate": 0.4273
            }
          }
        }
      ]
    }
  }
}

Since our above script was very simple (i.e. multiply a field by some factor), the simplest way would involve using field_value_factor and it goes like this:

{
  "query": {
    "function_score": {
      "query": {
        ...                              <--- your query goes here
      },
      "functions": [
        {
          "field_value_factor": {
            "field": "revenue_amount",
            "boost_mode": "replace",
            "factor": 0.4273             <--- insert the conversion rate here
          }
        }
      ]
    }
  }
}

UPDATE

According to your latest comment, it seems that the right option for you is to use script_score after all. The idea here would be to input all your currency rates available in your lookup table as parameters of your script_score script and then use the proper one according to the value of the revenue_currency field.

{
  "query": {
    "function_score": {
      "query": {},
      "functions": [
        {
          "script_score": {
            "script": "doc.revenue_amount.value * (doc.revenue_currency.value == 'EUR' ? EUR : (doc.revenue_currency.value == 'AUD' ? AUD : 1))",
            "boost_mode": "replace",
            "params": {
              "EUR": 0.4945,
              "AUD": 0.5623
            }
          }
        }
      ]
    }
  }
}