Snowflake读取JSON遇空值问题：如何提取ident数组中的指定字段值

问题原因 #

你的JSON结构中，ident是字符串数组，每个元素是key:value格式的普通字符串，并非嵌套JSON对象。之前的查询用JSON_EXTRACT_PATH_TEXT直接查找Product或Product[0].Product，路径完全错误，所以返回null。

另外，你的INSERT语句存在语法错误，缺少闭合的括号，修正后才能正常执行：

INSERT INTO SAMPLE_J(SAMPLE_JSON)
VALUES ('{"ident":["Product:Tires","sender_id:12345","sender_name:John Who","Tire_Condition:New"]}');

正确处理步骤 #

1. 展开数组元素

先用FLATTEN函数将ident数组拆分为多行，每行对应一个key:value字符串：

SELECT 
  s.SAMPLE_JSON,
  f.value AS ident_item
FROM SAMPLE_J s,
LATERAL FLATTEN(INPUT => PARSE_JSON(s.SAMPLE_JSON):ident) f;

执行后会输出每行对应一个数组元素的结果。

2. 拆分键值对

用SPLIT_PART按:拆分字符串，分离出键和值：

SELECT 
  s.SAMPLE_JSON,
  SPLIT_PART(f.value, ':', 1) AS key_name,
  SPLIT_PART(f.value, ':', 2) AS key_value
FROM SAMPLE_J s,
LATERAL FLATTEN(INPUT => PARSE_JSON(s.SAMPLE_JSON):ident) f;

执行后会得到拆分后的键、值两列数据。

3. 行转列（生成目标列）

如果需要将键值对转为列格式，使用PIVOT完成行转列：

WITH parsed_data AS (
  SELECT 
    SPLIT_PART(f.value, ':', 1) AS key_name,
    SPLIT_PART(f.value, ':', 2) AS key_value
  FROM SAMPLE_J s,
  LATERAL FLATTEN(INPUT => PARSE_JSON(s.SAMPLE_JSON):ident) f
)
SELECT 
  *
FROM parsed_data
PIVOT(MAX(key_value) FOR key_name IN ('Product', 'sender_id', 'sender_name', 'Tire_Condition')) AS p;

最终会生成你需要的结构化列数据。

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