如何在Snowflake中提取JSON中包含_sum的所有值？

问题说明 #

需要从Snowflake的JSON数据中提取_sum字段的值，示例JSON数据如下：

{"260196":7877,"260206":2642,"260216":7620,"260226":7560,"_sum":25699}
{"260196":9804,"260206":9804,"260216":9487,"260226":9411,"_sum":38506}

期望输出结果：

尝试执行以下代码时触发错误：

TO_VARCHAR(GET_PATH(PARSE_JSON(x), '_sum'))

错误信息：100069 (22P02): Error parsing JSON: unknown keyword "N", pos 2

错误原因 #

这个错误是因为你的x列中存在不符合JSON规范的数据，比如字符串NULL或者其他非JSON格式的内容，导致PARSE_JSON函数无法正常解析。

解决方案 #

1. 过滤无效JSON行后提取 #

先筛选出能正常解析的JSON数据，再提取_sum字段：

SELECT
    '_sum' AS Total,
    TO_VARCHAR(GET_PATH(PARSE_JSON(x), '_sum')) AS Value
FROM your_table
WHERE TRY_PARSE_JSON(x) IS NOT NULL;

TRY_PARSE_JSON会对无效JSON返回NULL，配合WHERE条件过滤掉无法解析的行。

2. 保留所有行，无效行返回NULL #

如果需要保留所有数据行，对无法解析的行返回NULL，可以用：

SELECT
    '_sum' AS Total,
    TO_VARCHAR(GET_PATH(TRY_PARSE_JSON(x), '_sum')) AS Value
FROM your_table;

3. 更简洁的语法 #

Snowflake支持直接用:访问JSON属性，写法更简洁直观：

SELECT
    '_sum' AS Total,
    TO_VARCHAR(TRY_PARSE_JSON(x):_sum) AS Value
FROM your_table;

内容的提问来源于stack exchange，提问作者Биляна Митова