I have a table in Presto that looks like

What I'd like it to resemble as output of the query is:

Date    Currency    Rate
1/1/2022    USD 725.275
1/2/2022    USD 745.275
1/1/2022    GBP 29.275
1/2/2022    GBP 40.275
1/1/2022    CAD 0.713352
1/2/2022    CAD 0.813352

But can't seem to figure it out using the their documentation.

CodePudding user response：

Assuming Rates is column of JSON type (otherwise use json_parse) and you don't want to handle every currency "manually" with json_extract_scalar (json_extract_scalar(Rates, '$.USD'), json_extract_scalar(Rates, '$.GBP'), ...) - common way to handle "dynamic json is to cast it to map (for example map(varchar, double) in this case) and use unnest:

WITH dataset ( Date,Rates) AS (
    VALUES  ('1/1/2022', json '{"USD":"725.275","GBP":"29.275000000000002","CAD":"0.713352"}'),
            ('1/2/2022', json '{"USD":"745.275","GBP":"40.275000000000002","CAD":"0.813352"}')

) 

--query
select date, currence, rate
from dataset
cross join unnest(cast(rates as map(varchar, double))) t(currence, rate)

Output: