PrestoDB中解析VARCHAR类型JSON内的数字字段

原始表结构与数据 #

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

对应原始数据：

期望结果 #

需要将coordinates字段中的x、y数值单独提取，得到如下结构：

解决方案 #

针对PostgreSQL #

利用jsonb类型的操作符提取字段，并转换为数值类型：

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 
    event_date,
    (coordinates::jsonb ->> 'x')::numeric AS x,
    (coordinates::jsonb ->> 'y')::numeric AS y
FROM my_table;

• coordinates::jsonb：将字符串格式的JSON转为jsonb类型，支持更高效的JSON操作
• ->>：提取JSON字段值并转为文本类型
• ::numeric：将文本转为数值类型，确保结果为数字格式

针对MySQL #

使用JSON_EXTRACT函数提取字段并转换类型：

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 
    event_date,
    CAST(JSON_EXTRACT(coordinates, '$.x') AS DECIMAL) AS x,
    CAST(JSON_EXTRACT(coordinates, '$.y') AS DECIMAL) AS y
FROM my_table;

内容的提问来源于stack exchange，提问作者Smasell