如何在dbt（Jinja/SQL）中提取Redshift JSON字段的嵌套值

要实现你想要的提取效果，核心是利用Redshift的JSON函数将动态顶层键展开为多行，再提取嵌套的name和type字段，以下是具体的dbt模型代码：

WITH parsed_json AS (
    SELECT
        id,
        -- 将字符串类型的JSON解析为可操作的JSON对象
        JSON_PARSE(json_blob) AS json_obj
    FROM your_source_table -- 替换为你的源表名称
),
unnested_keys AS (
    SELECT
        id,
        -- 提取所有顶层键并拆分为单独行（对应Python内层循环逻辑）
        UNNEST(JSON_KEYS(json_obj)) AS top_level_key
    FROM parsed_json
)
SELECT
    uk.id,
    -- 提取每个顶层键下的name值并转换为整数类型
    JSON_EXTRACT_PATH_TEXT(pj.json_obj, uk.top_level_key, 'name')::INT AS name,
    -- 提取每个顶层键下的type值
    JSON_EXTRACT_PATH_TEXT(pj.json_obj, uk.top_level_key, 'type') AS type
FROM unnested_keys uk
JOIN parsed_json pj ON uk.id = pj.id
ORDER BY uk.id;

代码说明： #

1. parsed_json CTE：通过JSON_PARSE把字符串格式的json_blob转换为Redshift可操作的JSON对象，为后续提取键和值做准备。
2. unnested_keys CTE：用JSON_KEYS获取JSON对象的所有顶层键，得到一个键数组；再通过UNNEST将数组拆分为多行，这一步等价于你Python代码中遍历每个顶层键的内层循环。
3. 最终查询：关联两个CTE，通过JSON_EXTRACT_PATH_TEXT根据动态顶层键逐层提取name和type字段，同时完成数据类型转换。

如果你的Redshift版本支持->>操作符，也可以简化提取逻辑：

-- 替换最终查询中的提取语句
pj.json_obj->uk.top_level_key->>'name'::INT AS name,
pj.json_obj->uk.top_level_key->>'type' AS type

额外提示： #

• 若json_blob存在格式不合法的情况，可先用JSON_VALID函数做校验过滤，避免解析报错。
• 如果name或type字段可能缺失，可添加COALESCE函数处理空值，例如COALESCE(..., '未定义')。

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