I am having the following issue. I have a table like the following that holds all the exchange rates per day for all currencies (as base currency is Euro):

The column rates_values is in JSON. I have another table which is like the following one:

I join these 2 tables on date. As next step, I want to extract the value that is referred on the currency column from the rates_values column but I don't know how. A desirable result would be the below:

I can write a really long CASE...WHEN... statement and each time extract the matching value but that's not the optimal solution.

CodePudding user response：

This can be simply achieved by using the column as the JSON key when getting the appropriate rate:

(rates_values->currency)::float

(currency is the column from the second table in the question).

CodePudding user response：

You may first normalize (or flatten) the exchange rates table like this (exchange_rates CTE is a mimic of the real table):

with exchange_rates(id, rates_values, rate_date) as
(
 values
 (1, '{"AED":2.835349,"AFN":67.743417,"BGN":1.95582}', '2022-01-01'::date),
 (2, '{"AED":2.485349,"AFN":66.843814,"BGN":1.95582}', '2022-02-02')
)
select xr.id, l.currency, l.rate::numeric, xr.rate_date 
from exchange_rates xr
cross join lateral jsonb_each_text(xr.rates_values::jsonb) as l(currency, rate);

and then join with it as you need.