如何从S3嵌套JSON自动解析所有列表数据至行并生成SYSTEM列

核心思路是先提取data对象下的所有列表名称，再逐个展开每个列表的项，同时将列表名存入SYSTEM列。以下针对常用云数据仓库SQL引擎给出修改方案：

Athena/Presto 示例 #

假设原仅解析level1的SQL如下：

SELECT 
  'level1' AS SYSTEM,
  item.*
FROM your_s3_table,
  UNNEST(data.level1) AS t(item)

修改后自动识别所有列表的SQL：

SELECT 
  system_name AS SYSTEM,
  item.*
FROM your_s3_table,
  -- 提取data下的所有列表名称
  UNNEST(CAST(MAP_KEYS(data) AS ARRAY<VARCHAR>)) AS t(system_name),
  -- 根据列表名称展开对应数组的每个项
  UNNEST(CAST(data[system_name] AS ARRAY<JSON>)) AS t2(item)
WHERE 
  -- 过滤掉data下非数组类型的键（若存在）
  JSON_TYPEOF(data[system_name]) = 'array'

Redshift 示例 #

Redshift的JSON函数语法略有差异，修改后的SQL如下：

SELECT 
  system_name AS SYSTEM,
  JSON_PARSE(item) AS item_detail
FROM your_s3_table,
  -- 获取data对象的所有键
  UNNEST(JSON_OBJECT_KEYS(data)) AS t(system_name),
  -- 展开对应列表的每个项
  UNNEST(JSON_EXTRACT_PATH_ARRAY(data, system_name)) AS t2(item)
WHERE 
  -- 确保当前键对应的值是数组
  JSON_TYPEOF(JSON_EXTRACT_PATH(data, system_name)) = 'array'

注意事项 #

• 确保data对象下需要解析的字段均为数组类型，若存在非数组字段，需通过WHERE子句过滤，避免UNNEST报错
• 不同SQL引擎的JSON函数语法存在差异，需根据实际使用的引擎调整：
  • BigQuery 使用JSON_KEYS提取键，JSON_EXTRACT_ARRAY提取数组
  • Snowflake 使用OBJECT_KEYS提取键，PARSE_JSON转换数组

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