使用dbt在Snowflake中处理JSON数组转字典遇解析错误求助

错误根源 #

1. 列名被当作字符串解析：原宏中把传入的params列名用单引号包裹，生成的SQL是PARSE_JSON('params')，这会尝试将字符串"params"解析为JSON（而非引用表中的params列），自然触发JSON解析错误。
2. 宏用法错误：错误使用run_query函数，该函数用于在dbt编译阶段执行独立查询，而非生成嵌入模型的逐行转换逻辑，无法实现每行JSON数组的转换需求。

修正后的dbt宏 #

这个宏会生成用于逐行转换的SQL片段，直接嵌入模型的SELECT语句中：

{% macro array_to_dict(arr_col, key_field, value_field) %}
    -- 若arr_col是字符串类型（非VARIANT），将INPUT改为PARSE_JSON({{ arr_col }})
    OBJECT_AGG(
        f.value:{{ key_field }},
        f.value:{{ value_field }}
    )
    FROM LATERAL FLATTEN(INPUT => {{ arr_col }}) AS f
{% endmacro %}

模型中的正确调用 #

由于OBJECT_AGG是聚合函数，需要按表中的唯一标识列（如id）分组：

SELECT
    id,
    {{ array_to_dict('params', 'key', 'value') }} AS _params
FROM test
GROUP BY id

补充说明 #

• 如果你的params列是字符串类型（未存储为Snowflake的VARIANT类型），请修改宏中的FLATTEN输入部分为：INPUT => PARSE_JSON({{ arr_col }})
• 确保key_field和value_field与JSON数组中对象的字段名完全一致，无需额外添加引号

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