如何在Snowflake中使用LATERAL FLATTEN动态提取JSON值？

云上老周

2026-6-22

动态提取JSON中的问答对（Snowflake实现）

问题背景

现有JSON数据结构如下：

{"explosives_UG":{"isCritical": false,"value": "N/A"},"explosivesUG": {"comment": "Test 619 313","createWo": false,"isCritical": false,"value": "No"},"generalAttachment": null,"generalComment": "Test 619 313","guardsPostedUG": {"isCritical": false,"value": "Yes"},"imminentUG": {"isCritical": false,"value": "Yes"}}

此前通过手动指定Key的方式提取问答对，查询语句如下：

SELECT kv.key AS question,kv.value:value AS answer
FROM json_data,
LATERAL FLATTEN(input => json_col) kv
WHERE kv.key IN ('explosives_UG', 'explosivesUG', 'guardsPostedUG', 'imminentUG');

得到输出：

question        answer
explosives_UG   N/A
explosivesUG    No
guardsPostedUG  Yes
imminentUG      Yes

由于需要处理大量问答内容，手动维护Key耗时费力，希望通过LATERAL FLATTEN动态提取符合要求的问答对。

解决方案

可以通过两种方式实现动态提取：

方式1：自动识别含`value`字段的嵌套对象

直接筛选JSON中值为对象且包含value属性的键，无需手动指定目标Key：

SELECT 
    kv.key AS question,
    kv.value:value AS answer
FROM json_data,
LATERAL FLATTEN(input => json_col) kv
WHERE 
    kv.value::OBJECT IS NOT NULL 
    AND kv.value:value IS NOT NULL;

方式2：排除固定非目标字段

如果存在明确不需要的字段（比如generalAttachment、generalComment），直接排除这些Key即可：

SELECT 
    kv.key AS question,
    kv.value:value AS answer
FROM json_data,
LATERAL FLATTEN(input => json_col) kv
WHERE 
    kv.key NOT IN ('generalAttachment', 'generalComment')
    AND kv.value:value IS NOT NULL;