使用Snowflake SQL提取JSON对象中mobDeviceTypeId的键值对并存储到表中

我完全理解你的困扰——当JSON对象的键是动态变化的时候，常规的静态路径提取根本行不通。之前用FLATTEN没得到预期结果，大概率是没用到正确的模式参数。别担心，下面就给你详细的解决方案：

核心思路 #

Snowflake的FLATTEN函数不仅可以展开数组，还能通过指定MODE => 'OBJECT'来展开JSON对象，把每个键值对拆分成单独的行，这样就能轻松获取动态的键和对应的值了。

具体实现 #

假设你的原始JSON数据存储在某个表（比如raw_api_data）的json_payload字段中，包含displayValues结构，那么可以用以下SQL来提取并转换：

-- 提取动态键值对并转换为id、device列
SELECT
    -- 将键转换为整数类型（根据实际需求调整，也可以保留字符串）
    f.key::INTEGER AS id,
    -- 提取对应的值作为设备名称
    f.value::STRING AS device
FROM
    raw_api_data,
    -- 展开mobDeviceTypeId对象，模式指定为OBJECT
    LATERAL FLATTEN(INPUT => json_payload:displayValues:mobDeviceTypeId, MODE => 'OBJECT') f;

如果需要直接将结果存入新表，可以用CREATE TABLE AS SELECT：

-- 创建新表并插入数据
CREATE TABLE mob_device_types (
    id INTEGER,
    device STRING
) AS
SELECT
    f.key::INTEGER AS id,
    f.value::STRING AS device
FROM
    raw_api_data,
    LATERAL FLATTEN(INPUT => json_payload:displayValues:mobDeviceTypeId, MODE => 'OBJECT') f;

针对单个JSON字符串的场景 #

如果你的数据是从API获取的单个JSON字符串（不是存在表中），可以先用PARSE_JSON解析后再处理：

WITH parsed_json AS (
    SELECT PARSE_JSON('{
        "displayValues": {
            "mobDeviceTypeId": {
                "0": "Unknown",
                "2": "Smart Phone",
                "4": "Tablet",
                "-1": "Name is not available (-1)",
                "-7": "NA"
            }
        }') AS json_data
)
SELECT
    f.key::INTEGER AS id,
    f.value::STRING AS device
FROM
    parsed_json,
    LATERAL FLATTEN(INPUT => json_data:displayValues:mobDeviceTypeId, MODE => 'OBJECT') f;

避坑提示 #

1. 模式参数必须指定：默认情况下FLATTEN是处理数组的，所以一定要加上MODE => 'OBJECT'才能展开JSON对象。
2. 类型转换容错：如果你的键中存在无法转换为整数的情况（比如非数字字符串），可以用TRY_CAST代替强制转换，避免查询失败：

   TRY_CAST(f.key AS INTEGER) AS id
   

3. LATERAL关键字：必须搭配LATERAL才能让FLATTEN和主表的每行数据关联起来，否则会出问题。

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