Snowflake表中列存储的JSON字符串展开与结构化转换方法

架构老林

2026-6-14

在Snowflake中展开JSON数组为行的实现方案

原始表结构

Col1	Col2	Col3	Col4
1	foo	32	[{"id":"1", "category":"black"}]
2	bar	22	[{"id":"1", "category":"black"}, {"id":"4", "category":"white"}]
3	smeg	2	null

目标表结构

Col1	Col2	Col3	id	category
1	foo	32	1	black
2	bar	22	1	black
2	bar	22	4	white
3	smeg	2	null	null

解决方案SQL

SELECT 
    t.Col1,
    t.Col2,
    t.Col3,
    f.value:id::STRING AS id,
    f.value:category::STRING AS category
FROM 
    your_table_name t
OUTER LATERAL FLATTEN(
    input => PARSE_JSON(t.Col4)
) f;

关键说明

PARSE_JSON(t.Col4)：将字符串格式的JSON数据转换为Snowflake支持的半结构化数据类型，确保后续可以解析数组和对象。
OUTER LATERAL FLATTEN(...)：对应SQL Server中的OPENJSON，用于将JSON数组展开为多行记录。OUTER关键字保证原始表中Col4为null的行不会被过滤，而是保留并生成id和category为null的结果。
f.value:id::STRING：从展开后的JSON对象中提取id字段，并显式转换为字符串类型，避免因数据类型不匹配出现异常。category字段的提取逻辑同理。