Parse numbers in varchar SQL, PrestoDB

Question

I have my table like this:

WITH my_table (event_date, coordinates) AS (
    values 
    ('2021-10-01','{"x":"1.0","y":"0.049"}'),
    ('2021-10-01','{"x":"0.0","y":"0.865"}'),
    ('2021-10-02','{"y":"0.5","x":"0.5"}'),
    ('2021-10-02','{"y":"0.469","x":"0.175"}'),
    ('2021-10-02','{"x":"0.954","y":"0.021"}')
) 

SELECT *
FROM my_table

event_date	coordinates
2021-10-01	{"x":"1.0","y":"0.049"}
2021-10-01	{"x":"0.0","y":"0.865"}
2021-10-02	{"y":"0.5","x":"0.5"}
2021-10-02	{"y":"0.469","x":"0.175"}
2021-10-02	{"x":"0.954","y":"0.021"}

I want to parse x and y fields separately Desired table should look like this:

event_date	x	y
2021-10-01	1.0	0.049
2021-10-01	0.0	0.865
2021-10-02	0.5	0.5
2021-10-02	0.469	0.175
2021-10-02	0.954	0.021

Thanks for helping me!

Answer 1

Use json_extract_scalar with corresponding json path:

SELECT event_date,
       json_extract_scalar(coordinates,'$.x') as x,
       json_extract_scalar(coordinates,'$.y') as y
FROM my_table;

Output:

event_date	x	y
2021-10-01	1.0	0.049
2021-10-01	0.0	0.865
2021-10-02	0.5	0.5
2021-10-02	0.175	0.469
2021-10-02	0.954	0.021