Snowflake使用Lateral Flatten提取嵌套JSON的statusCategory的id

问题原因 #

你之前的查询用了两次LATERAL FLATTEN，但flatten函数是用来展开JSON数组的，而你的statusCategory是单个JSON对象（非数组结构），对单个对象执行flatten不会返回任何行，这就是查询无输出的根本原因。

针对单个对象的解决方案 #

如果status和statusCategory始终是单个对象（不是数组），直接通过JSON路径访问即可，不需要使用flatten：

SELECT 
    parse_json(FULL_PAYLOAD):fields:status:name::varchar AS Derivedstatus,
    parse_json(FULL_PAYLOAD):fields:status:statusCategory:id::integer AS Derivedid
FROM SnowflakeTable A;

• 路径说明：parse_json(FULL_PAYLOAD)将字符串类型的FULL_PAYLOAD转为JSON对象，之后通过:逐层访问嵌套字段
• 类型转换：根据字段实际类型用::integer或::varchar做显式转换

针对数组场景的扩展方案 #

如果status或statusCategory是数组结构（比如存在多个status对象），才需要用flatten展开：
比如当status是数组时：

SELECT 
    s.value:name::varchar AS Derivedstatus,
    s.value:statusCategory:id::integer AS Derivedid
FROM SnowflakeTable A,
LATERAL FLATTEN(input => parse_json(FULL_PAYLOAD):fields:status) s;

如果statusCategory本身是数组：

SELECT 
    parse_json(FULL_PAYLOAD):fields:status:name::varchar AS Derivedstatus,
    sc.value:id::integer AS Derivedid
FROM SnowflakeTable A,
LATERAL FLATTEN(input => parse_json(FULL_PAYLOAD):fields:status:statusCategory) sc;

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