I have two separate indexes -
1. products
2. currency_rates
documents in the product index, have the following details -
{
"prod_id" : 1,
"currency" : "USD",
"price" : 1
}
documents in the currency_rates index, have the following details -
{
"id" : 1,
"USD" : 1,
"SGD" : 0.72,
"MYR" : 0.24,
"INR" : 0.014,
"EUR" : 1.12
}
I wish to achieve sorting on products index's price field,
but because every document in the product index might have different currencies,
I need to first convert all the currencies into USD,
And the carryout sorting on the converted resultset.
Eg.-
products -
[{
"prod_id": 1,
"currency": "USD",
"price": 1
}, {
"prod_id": 2,
"currency": "INR",
"price": 60
}]
currency_rates -
{
"USD": 1,
"SGD": 0.72,
"MYR": 0.24,
"INR": 0.014,
"EUR": 1.12
}
Following are my creation queries -
GET curency_rates/_search
{
"query": {
"match_all": {}
}
}
PUT /curency_rates/_doc/1
{
"id":1,
"USD" : 1,
"SGD" : 0.72,
"MYR" : 0.24,
"INR" : 0.014,
"EUR" : 1.12
}
PUT /products/_doc/1?pretty
{
"prod_id":1,
"currency": "USD",
"price": 1
}
PUT /products/_doc/2?pretty
{
"prod_id":2,
"currency": "INR",
"price": 60
}
GET products/_search
{
"query": {
"match_all": {}
}
}
Found that the following is a very similar use-case to mine,
But I couldn't understand how the are fetching conversion factor from another index at run time and, then using it in their compound query -
Elastic Search sort preprocessing
I've come up with the following query,
Based on the answer in the above link I'm referring -
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.EUR"
}
}
}]
}
}
}
But I'm getting the wrong result -
{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 4,
"relation" : "eq"
},
"max_score" : 67.2,
"hits" : [
{
"_index" : "products",
"_type" : "_doc",
"_id" : "2",
"_score" : 67.2,
"_source" : {
"prod_id" : 2,
"currency" : "INR",
"price" : 60
}
},
{
"_index" : "products",
"_type" : "_doc",
"_id" : "3",
"_score" : 2.24,
"_source" : {
"prod_id" : 3,
"currency" : "EUR",
"price" : 2
}
},
{
"_index" : "products",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.12,
"_source" : {
"prod_id" : 1,
"currency" : "USD",
"price" : 1
}
},
{
"_index" : "products",
"_type" : "_doc",
"_id" : "5",
"_score" : 1.12,
"_source" : {
"prod_id" : 5,
"currency" : "MYR",
"price" : 1
}
}
]
}
}
References -
https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-function-score-query.html#function-script-score
https://qbox.io/blog/scoring-using-elasticsearch-scripts-part1
Query -
