Parse numbers in varchar SQL, PrestoDB-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

Try to parse the json data and extract each point alone.

Refer to this : Issues with JSON_EXTRACT in Presto for keys containing ' ' character

In your case, query should be :

select event_date,json_extract_scalar(coordinates,'$.attributes["x"]') as x, json_extract_scalar(coordinates,'$.attributes["y"]') as y;

UPDATE

Sorry i've forgot to remove "attributes" from the query as it was taken from the other answer :)

select event_date,json_extract_scalar(coordinates,'$.x') as x, json_extract_scalar(coordinates,'$.y') as y;