AWS Athena查询含类JSON数组字段返回空结果求助

你的数据是类JSON格式，并非标准JSON，这是导致json_parse报错、正则提取返回空结果的核心原因：

• 标准JSON要求用双引号，你的数据用了单引号
• 标准JSON用null表示空值，你的数据用了None
• 标准JSON用小写false，你的数据用了大写False

正确处理方案 #

先将类JSON字符串转换为标准JSON格式，再解析成数组并展开，最后提取目标字段：

SELECT
  json_extract_scalar(item, '$.emp') AS emp,
  json_extract_scalar(item, '$.indicator') AS indicator,
  json_extract_scalar(item, '$.Description') AS Description
FROM (
  SELECT
    -- 依次完成格式转换：单引号转双引号、None转null、False转false
    json_parse(
      replace(
        replace(
          replace(column3, chr(39), chr(34)),
          'None', 'null'
        ),
        'False', 'false'
      )
    ) AS json_array
  FROM "tablename"
  LIMIT 20
),
-- 展开JSON数组中的每个对象
unnest(json_array) AS t(item)

原方法失败原因说明 #

1. 正则提取空结果：你使用的正则'''Description''\s?:\s?\b(\w*)\b'仅能匹配纯字母数字的内容，但你的Description值包含冒号、空格，\w*无法匹配，因此返回空。
2. json_parse报错：原字符串不符合标准JSON语法规则，必须先完成格式替换才能正常解析。

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