Snowflake解析ADF导出的JSON Lines文件返回NULL问题咨询

• 现有流程链路：ADF分页调用Web API，将多页接口响应合并为单个JSON Lines（setOfObjects）格式文件存入Azure Blob，文件每行结构为{"values":[嵌套业务对象数组]}
• 异常表现：直接执行select $1:values:a::string from @dbo.stage_json_example/setOfObjects.json返回NULL
• 本质原因：values字段是JSON数组结构，不存在“根节点→values→a”的直接路径，Snowflake不会自动拆解嵌套数组的层级，直接查询匹配路径为空所以返回NULL

基础验证 #

首先确认Snowflake对目标文件的基础解析是否正常，执行查询校验：

-- 逐行读取JSON文件，确认每行内容可被正常识别
select $1
from @dbo.stage_json_example/setOfObjects.json;

执行后会返回2条有效记录（文件中的空行会被Snowflake自动跳过），与写入的两行JSON结构完全匹配，证明文件读取链路无问题。

数组展开解析 #

使用Snowflake内置的LATERAL FLATTEN函数，将values数组中的每个嵌套对象拆解为独立行，再提取对应业务字段：

select
  f.value:a::string as a,
  f.value:b::string as b,
  f.value:c::string as c
from @dbo.stage_json_example/setOfObjects.json as raw_file,
lateral flatten(input => raw_file.$1:values) as f;

执行后即可得到预期的4行结构化结果：

直接入表适配 #

如果需要将解析结果直接加载到Snowflake行式表，可将上述解析逻辑嵌入COPY INTO命令实现批量入表，无需额外修改ADF侧的单文件输出逻辑：

-- 示例：将解析后的数据直接写入目标表
copy into dbo.target_demo_table(a, b, c)
from (
  select
    f.value:a::string,
    f.value:b::string,
    f.value:c::string
  from @dbo.stage_json_example/setOfObjects.json as raw_file,
  lateral flatten(input => raw_file.$1:values) as f
)
file_format = (type = 'json');

• Snowflake默认JSON解析器会自动跳过文件中的空白行，不需要在ADF侧额外做空行清理
• 单文件场景下的数组展开操作解析开销极低，不需要拆分ADF输出为多文件即可保持加载效率
• 如果不使用数组展开机制，即使调整查询路径，也需要硬编码数组下标才能取值，无法自动覆盖数组长度变化的场景，适配性很差
• 若后续JSON结构调整为其他嵌套层级，只需要修改flatten函数的输入路径即可适配，不需要调整stage配置

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