Snowflake中无法从JSON提取数据问题求助

你的核心问题在于ITC字段的结构：它是一个包含JSON字符串的数组，每个数组元素是转义后的JSON文本（比如"{\"category\":\"a\"}"），而非直接的JSON对象。原查询的处理逻辑没有匹配这个结构，导致提取失败。

正确查询语句 #

SELECT DISTINCT 
    T_ID,
    ITC,
    PARSE_JSON(prop.VALUE):category::string AS cat
FROM MDCD.M.T01 a,
     LATERAL FLATTEN(input => a.ITC, outer => true) prop

逻辑说明 #

1. 拆分数组：先用LATERAL FLATTEN把ITC数组拆分成单个的JSON字符串元素，此时prop.VALUE就是每个独立的转义JSON文本（比如"{\"category\":\"a\"}"）。
2. 解析JSON字符串：用PARSE_JSON()将转义后的字符串转换成可操作的JSON对象。
3. 提取字段：通过冒号语法:category提取JSON对象中的category字段，再转成字符串类型得到最终结果。

原查询失败原因 #

• JSON_EXTRACT_PATH_TEXT(REPLACE(ITC,'\\',''), 'category')：REPLACE操作是对整个数组做转义去除，得到的是格式错误的JSON数组（比如["{"category":"a"}", ...]），且数组本身没有顶级的category字段，自然提取不到值。
• prop.VALUE::string：FLATTEN后prop.VALUE是原始的JSON字符串，不是JSON对象，直接转字符串还是原文本，无法提取内部的category字段。

可选优化（处理空值） #

如果需要兼容数组为空或元素异常的情况，可以添加判断逻辑：

SELECT DISTINCT 
    T_ID,
    ITC,
    CASE 
        WHEN prop.VALUE IS NOT NULL THEN PARSE_JSON(prop.VALUE):category::string 
        ELSE NULL 
    END AS cat
FROM MDCD.M.T01 a,
     LATERAL FLATTEN(input => a.ITC, outer => true) prop

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