Extract value from JSON that matches a column value

LaSanton 0
Viewed 49

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):

id rates_values rate_date
1 {"AED":2.835349,"AFN":67.743417,... 1-1-2022
2 {"AED":2.485349,"AFN":66.843814, 2-2-2022

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

currency cost date_of_sale
AED 150 1-1-2022
AFN 250 2-2-2022
EUR 56 2-2-2022

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:

currency extracted_values_for_each_sale
AED 2.835349
AFN 66.843814
EUR 1

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

sql postgresql
2 Answers

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).

user37388700

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);
id currency rate rate_date
1 AED 2.835349 2022-01-01
1 AFN 67.743417 2022-01-01
1 BGN 1.95582 2022-01-01
2 AED 2.485349 2022-02-02
2 AFN 66.843814 2022-02-02
2 BGN 1.95582 2022-02-02

and then join with it as you need.

Stefanov.sm0
Related