Snowflake SQL中解析含@为键、$为值的JSON数据方法咨询

步骤1：验证并解析JSON数据 #

首先确保原始JSON字符串格式合法，使用JSON_PARSE（或TRY_JSON_PARSE排查格式问题）将字符串转为Snowflake的VARIANT类型：

SELECT JSON_PARSE(raw_json) AS json_var FROM your_table;

如果解析失败，检查原始数据是否存在未转义的引号、非法字符等，可通过REPLACE修复，例如：

SELECT JSON_PARSE(REPLACE(raw_json, '\"', '"')) AS json_var FROM your_table;

步骤2：展开并转换键值对 #

通过多层LATERAL FLATTEN展开数组，将JSON中的嵌套结构转换为扁平的键值对，同时处理特殊键（如"]@"）和数组类型的$字段：

WITH parsed_data AS (
  SELECT JSON_PARSE(raw_json) AS json_var FROM your_table
),
flattened_top AS (
  SELECT f.value AS top_obj
  FROM parsed_data, LATERAL FLATTEN(input => json_var) f
),
key_value_pairs AS (
  SELECT
    -- 处理$字段：数组则展开子元素为键值对，标量则用顶级@（或特殊键"]@"）作为键
    CASE 
      WHEN ARRAY_SIZE(top_obj:'$') > 0 THEN 
        (SELECT ARRAY_AGG(OBJECT_CONSTRUCT(o.value:'@'::STRING, o.value:'$')) 
         FROM LATERAL FLATTEN(input => top_obj:'$') o)
      ELSE 
        [OBJECT_CONSTRUCT(
          COALESCE(top_obj:'@'::STRING, top_obj:'"]@"'::STRING), 
          top_obj:'$'
        )]
    END AS dollar_kv,
    -- 提取顶级对象中除$、@外的其他字段（如sa、vrm）
    (SELECT ARRAY_AGG(OBJECT_CONSTRUCT(k, top_obj[k])) 
     FROM LATERAL FLATTEN(input => OBJECT_KEYS(top_obj)) k 
     WHERE k NOT IN ('$', '@', '"]@"')) AS other_kv
),
combined_kv AS (
  SELECT FLATTEN(ARRAY_CAT(dollar_kv, other_kv)).value::OBJECT AS kv_obj
  FROM key_value_pairs
),
final_kv AS (
  SELECT
    OBJECT_KEYS(kv_obj)[0] AS key_name,
    kv_obj[OBJECT_KEYS(kv_obj)[0]] AS key_value
  FROM combined_kv
)

步骤3：转成宽表格式 #

使用PIVOT将扁平的键值对转换为期望的列结构：

SELECT *
FROM final_kv
PIVOT (MAX(key_value) FOR key_name IN (
  'agon', 'aged', 'sta', 'lasIn', 'conom', 'chus', 'pla', 'adMe', 
  'vm', 'sca', 'sa', 'vrm', 'id', 'name', 'actId', 'title', 'acId'
));

补充说明 #

• 上述SQL会将每个JSON对象的@（或特殊键"]@"）作为列名，$字段的内容作为对应列的值；
• 若$是数组，则将数组内每个子对象的@作为列名，$作为值；
• 顶级对象中额外的字段（如sa、vrm）会直接转为对应的列和值；
• 若列名不确定，可先查询所有可能的键名，再更新PIVOT中的列列表：

SELECT DISTINCT OBJECT_KEYS(kv_obj)[0] AS all_keys FROM combined_kv;

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