从Presto varchar列解析JSON失败

Question

sql prettyprint-override">WITH
data(c) AS (
    SELECT message from mydb.mytable
),
parsed AS (
    SELECT cast(json_parse(c) as map(varchar, varchar)) AS m
    FROM data
)
SELECT m['action'], m['uuid']
FROM parsed

Answer 1

json_parse+cast处理示例数据：

SELECT CAST(json_parse(str) AS map(varchar, varchar))
FROM (VALUES '{"action":"send","timestamp":1566432054,"uuid":"1234"}') t(str);

我在Presto317上测试了这个：

presto> SELECT CAST(json_parse(str) AS map(varchar, varchar))
     -> FROM (VALUES '{"action":"send","timestamp":1566432054,"uuid":"1234"}') t(str);
                     _col0
------------------------------------------------
 {action=send, uuid=1234, timestamp=1566432054}
(1 row)

我的猜测是某些数据行与您的示例不同，并且该数据行不能cast。您可以使用try找到它：

SELECT str
FROM your_table
WHERE str IS NOT NULL
AND try(CAST(json_parse(str) AS map(varchar, varchar))) IS NULL;