CODEWITHSUNDEEP
elasticsearch
Dev1ce
Dev1ce

Reputation: 5944

Elasticsearch: Unable to order results using script_score

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 - enter image description here test 2 - enter image description here null_pointer_exception - enter image description here

Upvotes: 1

Views: 796

Answers (1)

ymonad
ymonad

Reputation: 12090

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

Upvotes: 2

Related Questions