Snowflake DBT中提取JSON字符串字段ER值失败的技术求助

我在Snowflake中有一个名为int_calcs的列，存储的是字符串格式的JSON数据，内容如下（原数据中的"为HTML转义双引号，实际是标准JSON格式）：

{
  "frequency": {
    "SA": {
      "ER": 1.00,
      "EXR": 0.18667007686333767,
      "CW": 0.18667007686333767
    }
  }
}

我尝试在DBT中提取其中的ER值（1.00），但遇到问题，先后尝试了两种写法：

• 第一种写法（语法正确但未提取到值）：

SELECT
    lower(cast(json_extract_path_text(parse_json(int_calcs), '$.frequency.SA.ER') as float)) as ER

该写法语法合法，但未提取到目标值，导致dbt_utils.at_least_one测试失败。

• 第二种写法（存在语法错误）：

lower(cast(parse_json(int_calcs:frequency:SA:ER::number))) as ER

针对你的需求，提供两种正确的实现方式：

方法一：修正json_extract_path_text的路径格式 #

json_extract_path_text的路径参数不需要带$前缀，直接传递层级路径即可，同时数值类型无需使用lower()（无意义转换），调整后的SQL：

SELECT
    cast(json_extract_path_text(parse_json(int_calcs), 'frequency', 'SA', 'ER') as float) as ER

也可以使用点分隔的路径写法（同样去掉$）：

SELECT
    cast(json_extract_path_text(parse_json(int_calcs), 'frequency.SA.ER') as float) as ER

方法二：使用Snowflake JSON路径运算符（:） #

使用:访问JSON属性时，需先通过parse_json将字符串转为JSON对象，再逐层访问，同时修正语法错误：

SELECT
    parse_json(int_calcs):frequency:SA:ER::float as ER

如果偏好cast写法，正确格式为：

SELECT
    cast(parse_json(int_calcs):frequency:SA:ER as float) as ER

关键注意事项 #

• 确保int_calcs列的JSON字符串格式合法，避免出现原示例中末尾多余逗号这类无效JSON结构。
• 数值类型不需要调用lower()，该函数仅对字符串有效，会造成不必要的转换。

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