Passing dynamic value to script query in Elastic Search

Question

I have two mappings in my index. One of them stores some amount in different currencies and other stores current conversion rate. Records in each look like this:

http://localhost:9200/transactions/amount

[{
    _index: "transactions",
    _type: "amount",
    _id: "AVA3fjawwMA2f8TzMTbM",
    _score: 1,
    _source: {
        balance: 1000,
        currency:"usd"
    }
},
{
    _index: "transactions",
    _type: "amount",
    _id: "AVA3flUWwMA2f8TzMTbN",
    _score: 1,
    _source: {
        balance: 2000,
        currency:"inr"
    }
}]

and

http://localhost:9200/transactions/conversions

{
    _index: "transactions",
    _type: "conversions",
    _id: "rates",
    _score: 1,
    _source: {
        "usd": 1,
        "inr":62.6
    }
}

I want to query the data from amount and apply current conversion rates from conversions in a single query and get result.

I tried using scripted query and was able to convert the data based on passed params like:

GET _search

{
   "query": {
      "match_all": {}
   },
   "script_fields" : {
        "test1" : {
            "script" : "_source.balance * factor",
            "params" : {
                "factor"  : 63.2
            }
        }
    }
}

However in my case passed params are to be fetched from result of another query.

I want to visualize my data in Kibana in common currency. Kibana supports scripted queries. As per my knowledge all visualizations in Kibana can correspond to a single elastic search query so I don't have an option to do multiple queries.

I also tried exploring the possibility of using https://www.elastic.co/blog/terms-filter-lookup and adding some dynamic fields to each document in result set. However I don't think term filter allows that.

Answer 1

Assuming, you're trying to always plot transactions in USD, you could try the approach described in the accepted answer here:

In essence:

1. Model your data parent-child with each conversions document being a parent of all child transactions document in the same foreign currency. (And conversions having a standard fieldname like "conversion_divisor": 62.6)
2. Include a has_parent query clause for all relevant currency conversions.
3. Use a function_score (script_score) query to access the foreign currency multiple in each parent and generate a _score for each transaction by dividing the transaction amount by the foreign currency conversion_divisor.
4. Plot the _score in Kibana