Elasticsearch: Unable to order results using script_score

Question

I have a field called price in my elasticsearch index whose values I need to convert before sorting -

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "products",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "prod_id" : 1,
          "currency" : "USD",
          "price" : 1
        }
      },
      {
        "_index" : "products",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "prod_id" : 2,
          "currency" : "INR",
          "price" : 60
        }
      },
      {
        "_index" : "products",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "prod_id" : 3,
          "currency" : "EUR",
          "price" : 2
        }
      },
      {
        "_index" : "products",
        "_type" : "_doc",
        "_id" : "5",
        "_score" : 1.0,
        "_source" : {
          "prod_id" : 5,
          "currency" : "MYR",
          "price" : 1
        }
      }
    ]
  }
}

As the currency field for each product is different,
I plan to convert each product's price into USD,
Using the script_score function, as recommended here -
Elastic Search sort preprocessing

I tried the following query -

GET products/_search
{
    "query": {
        "function_score": {
            "query": {
                "match_all": {}
            },
            "functions": [{
                "script_score": {
                    "script": {
                        "params": {
                            "USD": 1,
                            "SGD": 0.72,
                            "MYR": 0.24,
                            "INR": 0.014,
                            "EUR": 1.12
                        },
                        "source": "doc['price'].value * params.doc['currency']"
                    }
                }
            }]
        }
    },
    "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ]
}

I'm getting an error -

{
  "error": {
    "root_cause": [
      {
        "type": "script_exception",
        "reason": "runtime error",
        "script_stack": [
          "doc['price'].value * params.doc['currency']",
          "                               ^---- HERE"
        ],
        "script": "doc['price'].value * params.doc['currency']",
        "lang": "painless"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "products",
        "node": "5-fQ27BhSUKycVJ2SwyH4A",
        "reason": {
          "type": "script_exception",
          "reason": "runtime error",
          "script_stack": [
            "doc['price'].value * params.doc['currency']",
            "                               ^---- HERE"
          ],
          "script": "doc['price'].value * params.doc['currency']",
          "lang": "painless",
          "caused_by": {
            "type": "class_cast_exception",
            "reason": "Cannot apply [*] operation to types [java.lang.Long] and [org.elasticsearch.index.fielddata.ScriptDocValues.Strings]."
          }
        }
      }
    ]
  },
  "status": 400
}

Expected product sequence -
1. prod_id 3, _score 2.24 = 2(price) * 1.12 (USD multiplier)
2. prod_id 1, _score 1 = 1(price) * 1 (USD multiplier) = 1
3. prod_id 2, _score 0.84 = 60(price) * 0.014 (USD multiplier)
4. prod_id 5, _score 0.24 = 1(price) * 0.24 (USD multiplier)

Index Mapping -

{
  "mapping": {
    "properties": {
      "currency": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        },
        "fielddata": true
      },
      "price": {
        "type": "long"
      },
      "prod_id": {
        "type": "long"
      }
    }
  }
}

Can someone please help for this use case query?

test 1 - test 2 - null_pointer_exception -

The answer you referred is four years ago, so maybe outdated. Try placing "boost_mode":"replace" under function_score. Read the reference for more information. — ymonad
– ymonad, Commented Aug 13, 2019 at 2:11
params should be inside "script":{"params":...}. Read the reference for more information. — ymonad
– ymonad, Commented Aug 13, 2019 at 2:20
Also note that currency field should be keyword datatype if you want to compare equality. — ymonad
– ymonad, Commented Aug 13, 2019 at 4:29
I think params.doc['currency'] should be something likeparams[String.valueOf(doc.currency)] as found in this question: Elasticsearch scripted fields. Dynamic param selection — arkuuu
– arkuuu, Commented Aug 13, 2019 at 6:52

ymonad · Accepted Answer · 2019-08-13 08:48:43Z

2

Since the currency has type text, it is indexed with Standard Analyzer which converts it to lower case.

That means, when currency is MYR, it is indexed as myr, so the script should be something like:

doc.currency.value == 'myr' ? params.MYR : 1

If currency is "type":"keyword", then you can use:

doc.currency.value === 'MYR' ? params.MYR : 1

answered Aug 13, 2019 at 8:48

ymonad

12.1k1 gold badge41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dev1ce Over a year ago

any way to shorten this -

"source" : "doc['price'].value * (doc.currency.value == 'eur'? params.EUR : doc.currency.value == 'myr' ? params.MYR : doc.currency.value == 'inr' ? params.INR : 1)"

cause I will endup having 20-30 currencies :/

ymonad Over a year ago

Maybe yes, but please post it as different question.

Collectives™ on Stack Overflow

Elasticsearch: Unable to order results using script_score

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related