Question 114763 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Extract value from JSON that matches a column value

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.

Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search