Snowflake提取字符串包裹的嵌套JSON所有值的实现求助

原始JSON数据 #

[  
  {    
    "A": "[{\"A\":\"5854\"}]"  
  },  
  {    
    "B": "[{\"B\":\"5681\"}]"  
  },  
  {    
    "FYM": "[{\"200701\":\"202112\",\"\":\"177\"}]"  
  }
]

需求说明 #

需将上述数据转换为包含COL_NAME、VALUE、MIN_DT、MAX_DT的结构化表格：

• COL_NAME：对应原始JSON中的顶级键（A、B、FYM）
• VALUE：对应嵌套JSON中的业务值（5854、5681、177）
• MIN_DT/MAX_DT：仅针对FYM字段，分别取嵌套JSON中的日期范围键（200701）和对应值（202112），其他字段这两列为NULL

实现SQL #

WITH raw_data AS (
  SELECT PARSE_JSON('[{"A":"[{\"A\":\"5854\"}]"},{"B":"[{\"B\":\"5681\"}]"},{"FYM":"[{\"200701\":\"202112\",\"\":\"177\"}]"}]') AS json_data
),
flattened_top AS (
  SELECT 
    f.value AS top_obj
  FROM raw_data,
       LATERAL FLATTEN(input => json_data) f
),
extract_top_keys AS (
  SELECT 
    OBJECT_KEYS(top_obj)[0] AS col_name,
    PARSE_JSON(top_obj[OBJECT_KEYS(top_obj)[0]]) AS nested_json
  FROM flattened_top
),
final_parse AS (
  SELECT
    col_name,
    -- 提取VALUE字段，按不同列名匹配取值
    CASE 
      WHEN col_name IN ('A', 'B') THEN nested_json[0][col_name]::STRING
      WHEN col_name = 'FYM' THEN nested_json[0]['']::STRING
    END AS value,
    -- 仅FYM字段提取MIN_DT
    CASE 
      WHEN col_name = 'FYM' THEN OBJECT_KEYS(nested_json[0])[0]::STRING
      ELSE NULL
    END AS min_dt,
    -- 仅FYM字段提取MAX_DT
    CASE 
      WHEN col_name = 'FYM' THEN nested_json[0][OBJECT_KEYS(nested_json[0])[0]]::STRING
      ELSE NULL
    END AS max_dt
  FROM extract_top_keys
)
SELECT * FROM final_parse;

预期结果 #

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