Athena中将字符串转为JSON时含撇号字段的处理问题

你的核心问题是原数据属于非标准JSON格式：用单引号包裹键和部分字符串值，同时字符串内部本身包含撇号（单引号），全局替换单引号会破坏合法的字符串内容。以下是精准的处理方案：

关键思路 #

标准JSON要求：

• 对象的键必须用双引号包裹
• 字符串值用双引号包裹时，内部的单引号无需转义（可直接保留）

所以不需要替换字符串内部的单引号，只需要把包裹键和字符串值的单引号替换成双引号即可，用正则匹配精准定位这些单引号，避免误替换。

具体SQL代码 #

替换你原来的嵌套replace逻辑，改用regexp_replace精准处理：

CAST(
  json_extract(
    regexp_replace(
      regexp_replace(
        -- 替换为你的目标列名
        your_column_name,
        -- 匹配包裹键的单引号（比如 '{ 'name': ...' 里的 'name'）
        '([{,]\s*)''([^'']+)''(\s*:)',
        '$1"$2"$3'
      ),
      -- 匹配包裹字符串值的单引号（比如 ': 'Sam'' 里的 'Sam'）
      '(:\s*)''([^'']+)''(\s*[,}])',
      '$1"$2"$3'
    ),
    -- 保留你原来的JSON路径
    '$.items'
  ) AS array<json>
) AS column_list

代码说明 #

1. 第一个regexp_replace：专门匹配键的单引号——即大括号/逗号后、冒号前的单引号包裹内容，将其替换为双引号，比如'name': → "name":
2. 第二个regexp_replace：专门匹配字符串值的单引号——即冒号后、逗号/大括号前的单引号包裹内容，替换为双引号，比如: 'Sam' → : "Sam"
3. 字符串内部的单引号（比如"Toyota's Car"里的'）不会被匹配到，会完整保留，符合JSON规范
4. 处理后的字符串是标准JSON格式，可正常用json_extract和CAST解析

边界情况补充 #

如果你的键包含特殊字符（比如空格、连字符），可以调整第一个正则为'([{,]\s*)''(.*?)''(\s*:)'，用非贪婪匹配.*?适配任意键内容。

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