求助：Athena中如何展开嵌套数组格式的字符串列？

你的问题核心在于原始字符串是单引号格式的类JSON结构，而Athena的json_parse只支持标准双引号JSON，直接解析会失败。按以下步骤处理即可：

具体SQL实现 #

假设你的表名为your_table，包含id列和存储目标字符串的json_string_col列，执行以下SQL：

SELECT
  t.id,
  json_extract_scalar(item, '$.outer_key') AS outer_key
FROM
  your_table t,
  UNNEST(
    json_parse(
      replace(json_string_col, '''', '"') -- 将单引号替换为双引号，转为标准JSON
    )
  ) AS items(item)

关键步骤说明 #

• 替换单引号：用replace(json_string_col, '''', '"')把所有单引号换成双引号，将非标准的类JSON转为Athena可识别的标准JSON格式。
• 解析为数组：json_parse将处理后的字符串解析成JSON数组。
• 拆分行：UNNEST把数组中的每个元素拆分为单独的行。
• 提取字段：json_extract_scalar从每个JSON对象中提取outer_key的字符串值。

特殊情况处理 #

如果你的value值中包含单引号（比如{'key1': 'user's value'}），直接全局替换会破坏内容，这时需要用更精准的替换逻辑，只替换包裹键和结构的单引号：

SELECT
  t.id,
  json_extract_scalar(item, '$.outer_key') AS outer_key
FROM
  your_table t,
  UNNEST(
    json_parse(
      regexp_replace(
        json_string_col,
        '(?<=[{\[,])\'([^\']+)\'(?=[}\],:])', -- 匹配键或结构位置的单引号包裹内容
        '"\1"'
      )
    )
  ) AS items(item)

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