如何使用Snowflake SQL解析S3中嵌套数组JSON的cmsId和documentType？

JSON文件结构 #

S3中存储的JSON为外层数组嵌套子数组的结构，每个子数组包含多个字典对象：

[
    [
        {"cmsId": "63db9878d1", "documentType": "video"}, 
        {"cmsId": "ddd1", "documentType": "article"}
    ],
    [
        {"cmsId": "63db", "documentType": "blog"}, 
        {"cmsId": "63db987", "documentType": "article"}
    ]
]

需求 #

提取所有字典对象中的cmsId和documentType字段。

尝试的SQL语句（无输出） #

select
    *,
    metadata$filename as file_name,
    to_timestamp_ntz(current_timestamp()) as load_date
from
    @STG.S3/lean/incoming (FILE_FORMAT => STG.JSON_NO_OUTER_IVW, pattern=>'.*20230526_ids.gz' ) t,
    TABLE(FLATTEN(input => t.$1 , outer => false   ))

文件格式DDL #

{
   "TYPE":"JSON",
   "FILE_EXTENSION":null,
   "DATE_FORMAT":"AUTO",
   "TIME_FORMAT":"AUTO",
   "TIMESTAMP_FORMAT":"AUTO",
   "BINARY_FORMAT":"HEX",
   "TRIM_SPACE":false,
   "NULL_IF":[],
   "COMPRESSION":"AUTO",
   "ENABLE_OCTAL":false,
   "ALLOW_DUPLICATE":false,
   "STRIP_OUTER_ARRAY":true,
   "STRIP_NULL_VALUES":true,
   "IGNORE_UTF8_ERRORS":false,
   "REPLACE_INVALID_CHARACTERS":false,
   "SKIP_BYTE_ORDER_MARK":true
}

问题分析与解决方案 #

可能的无输出原因 #

1. 文件名/路径不匹配：检查S3文件是否符合pattern=>'.*20230526_ids.gz'规则，确认文件名包含20230526_ids.gz，且路径@STG.S3/lean/incoming正确。
2. 解析结构未适配：文件格式中STRIP_OUTER_ARRAY=true会展开最外层数组，表t的每行数据是一个子数组，原SQL未正确提取子数组内的字段值。

修正后的SQL语句 #

SELECT
    flatten_result.value:cmsId::STRING AS cms_id,
    flatten_result.value:documentType::STRING AS document_type,
    metadata$filename AS file_name,
    CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS load_date
FROM @STG.S3/lean/incoming (FILE_FORMAT => STG.JSON_NO_OUTER_IVW, pattern=>'.*20230526_ids.gz') t,
TABLE(FLATTEN(input => t.$1)) AS flatten_result

验证步骤 #

先执行基础查询确认文件是否被读取：

SELECT $1, metadata$filename
FROM @STG.S3/lean/incoming (FILE_FORMAT => STG.JSON_NO_OUTER_IVW, pattern=>'.*20230526_ids.gz')

如果无结果，优先排查路径、文件名匹配或Snowflake的S3访问权限问题。

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