在varchar SQL, PrestoDB中解析数字

Question

我的表是这样的:

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

tbody> <<tr>

event_date	坐标
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"}

尝试解析json数据并单独提取每个点

参考这个:Presto中包含'键的JSON_EXTRACT问题& # 39;字符

在你的例子中，查询应该是:

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

更新对不起，我忘记删除"属性"从查询，因为它是从另一个答案:)

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

使用json_extract_scalar与相应的json路径:

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

输出:

<表类>event_datexytbody><<tr>2021-10-011.00.0492021-10-010.00.8652021-10-020.50.52021-10-020.1750.4692021-10-020.9540.021