How to dynamically create scriting parameters based on another query?

[aniruddha.raje]

I have a query which contains a script for supporting sorting operation on an index,

The script requires few parameters, which I'm currently constructing in code

These parameters are being fetched from another index called currency_rates

I wanted to know if there is a way, I could do a subquery in the scripting to get the param values from it?

Here's the query I'm using -

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.keyword'].value]"
					}
				}
			}]
		}
	},
	"sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ]
}

The part of the query which I currently need to construct -

"params": {
	"USD": 1,
	"SGD": 0.72,
	"MYR": 0.24,
	"INR": 0.014,
	"EUR": 1.12
}

The source of these param values is an index called currency_rates-

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "curency_rates",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "id" : 1,
          "USD" : 1,
          "SGD" : 0.72,
          "MYR" : 0.24,
          "INR" : 0.014,
          "EUR" : 1.12
        }
      }
    ]
  }
}

I use the following query to fetch data from currency_rates -

GET /curency_rates/_search
{
  "query": {
    "match_all": {}
  }
}

[spinscale (Alexander Reelsen)]

hey,

you cannot do subqueries within another query. Caching this on the application side sounds like it makes most sense to me and then passing it as params like you did.

What you are trying to implement sounds like a classic pricing service to me, a common ecommerce requirement. While it looks easy for currency conversion to do within Elasticsearch, this service will become pretty complex, once certain users get different permanent discounts or you are starting to factor in local taxes. You might want to think to have such a component as its own service.

If those currencies change only once a day, maybe a full reindex with the correct currencies applied is another way to go, this way you would not need an expensive script being executed for every hit.

--Alex

[aniruddha.raje]

So I'll have to query my currency_rates index first and then build params object,
to be used in my sorting query, is it?
No other way to avoid 2 separate queries?

[spinscale (Alexander Reelsen)]

I would not query the currency index for every incoming request but rather every n minutes/seconds or have a mechanism to update that data structure in your client application. But you will need this data when executing your query.