dbt中使用Jinja在SQL模型内解析JSON列并拆分为多列的实现问题

错误原因 #

你写法的核心问题是混淆了Jinja的编译时机和SQL的执行时机：

• Jinja逻辑（{% set %}/{% do %}这类标签）是在dbt编译模型、生成SQL文本的阶段运行的，此时还没有和数据库交互，无法获取数据库表中data列的实际值
• 你写的jsonData只是SQL查询里的列别名，Jinja运行阶段这个变量根本没有被赋值，所以才会报Undefined错误

方案1：使用数据库原生JSON函数（最推荐） #

直接用你所用数据仓库的JSON解析函数在SQL层面处理，性能最高，也符合dbt模型的设计逻辑，无需拆分多个模型。
举个BigQuery环境的示例：

{{ config(materialized='table') }}

select
  time,
  tag,
  -- 提取JSON字段
  JSON_EXTRACT_SCALAR(data, '$.log') as log,
  JSON_EXTRACT_SCALAR(data, '$.path') as log_path,
  JSON_EXTRACT_SCALAR(data, '$.time') as log_ts,
  -- 判断key是否存在，不存在返回默认值
  IF(JSON_EXTRACT(data, '$.custom_key') IS NOT NULL, 
     JSON_EXTRACT_SCALAR(data, '$.custom_key'), 
     'default_value'
  ) as custom_key
from `warehouses.raw_data.customer_orders`
limit 5

不同数据仓库对应的JSON函数：

• Snowflake：用PARSE_JSON(data):key::string的语法
• PostgreSQL：用data::json->>'key'的语法
• Spark SQL：用get_json_object(data, '$.key')的语法

方案2：单模型内用Jinja动态解析JSON（适合JSON结构不固定的场景） #

如果你需要动态识别JSON里的所有key、自动生成所有列，可以在同一个模型内先调用dbt_utils.get_column_values读取源表的JSON数据，再动态生成SELECT逻辑：

{{ config(materialized='table') }}

-- 先读取源表的JSON字段值
{%- set json_rows = dbt_utils.get_column_values(
    table='`warehouses.raw_data.customer_orders`',
    column='data'
) -%}

-- 提取所有JSON里出现过的key
{%- set all_keys = set() %}
{% for json_str in json_rows %}
  {% set json_dict = fromjson(json_str) %}
  {% for key in json_dict.keys() %}
    {% do all_keys.add(key) %}
  {% endfor %}
{% endfor %}

select
  time,
  tag,
  -- 动态生成所有JSON字段的提取逻辑
  {% for key in all_keys %}
  JSON_EXTRACT_SCALAR(data, '$.{{ key }}') as {{ key }}{% if not loop.last %},{% endif %}
  {% endfor %}
from `warehouses.raw_data.customer_orders`

注意：这个方案需要先查询一次源表拉取JSON数据，如果源表数据量很大会有性能问题，仅适合JSON结构不固定、数据量较小的场景。

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