如何在Snowflake中解析类JSON结构VARCHAR列提取postScore值

你的写法存在两个核心问题：

1. 没有先将VARCHAR类型的TAG列显式解析为JSON/Variant类型，数据库默认只会把它当做普通字符串处理，无法按JSON结构取值
2. 你存储的结构中list字段是数组类型，必须先定位数组下标才能访问到下层的item字段，直接对数组取['item']无法匹配到值

不同数据库的实现写法 #

Snowflake（匹配你原写法的::类型转换语法） #

取数组中第一个元素的postScore值

SELECT PARSE_JSON(d.TAG)['list'][0]['item']['postScore']::INTEGER AS PostScore
FROM data d;

展开数组返回所有元素的postScore值

SELECT 
    value['item']['postScore']::INTEGER AS PostScore
FROM data d,
LATERAL FLATTEN(input => PARSE_JSON(d.TAG)['list'])

如果需要兼容非法JSON的脏数据，把PARSE_JSON替换为TRY_PARSE_JSON，解析失败会返回NULL不会报错。

PostgreSQL #

取数组中第一个元素的postScore值

SELECT (d.TAG::JSONB -> 'list' -> 0 -> 'item' ->> 'postScore')::INTEGER AS PostScore
FROM data d;

展开数组返回所有元素的postScore值

SELECT (elem -> 'item' ->> 'postScore')::INTEGER AS PostScore
FROM data d,
jsonb_array_elements(d.TAG::JSONB -> 'list') elem

MySQL 8.0+ #

取数组中第一个元素的postScore值

SELECT JSON_EXTRACT(JSON_PARSE(d.TAG), '$.list[0].item.postScore') AS PostScore
FROM data d;

展开数组返回所有元素的postScore值

SELECT j.PostScore
FROM data d,
JSON_TABLE(
    d.TAG,
    '$.list[*]' COLUMNS (
        PostScore INTEGER PATH '$.item.postScore'
    )
) j

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