如何在Athena中将Varchar类型嵌套JSON展开并提取值？

问题背景 #

从Google BigQuery爬取数据暂存到Athena后，某列以字符串类型存储嵌套JSON，格式如下：

{
   "key": "Category",
   "value": {
        "string_value": "something"
   }
}

需要将JSON展开扁平化，实现类似WHERE Category = 'something'的查询，提取key和string_value字段。

尝试以下SQL时返回null：

WITH dataset AS (
  SELECT cast(json_column as json) as json_column
  from "thedatabase"
  LIMIT 10
)
SELECT
    json_extract_scalar(json_column, '$.value.string_value') AS string_value
FROM dataset

原因是将字符串转为JSON类型后，内容出现多余的转义符和外层引号，例如：

"[{\"key\":\"something\",\"value\":{\"string_value\":\"app\"}}]"

且JSON类型无法使用replace函数清理，导致解析失败。

解决方案 #

方法1：先清理字符串再转JSON #

先通过字符串函数去掉外层引号和转义反斜杠，再转为JSON类型处理：

-- 针对外层是数组的情况
WITH dataset AS (
  SELECT 
    replace(replace(json_column, '\\', ''), '"[', '[') AS cleaned_json_str
  from "thedatabase"
  LIMIT 10
),
json_dataset AS (
  SELECT cast(replace(cleaned_json_str, ']"', ']') as json) as json_column
  FROM dataset
)
SELECT
    json_extract_scalar(json_column, '$.key') AS category_key,
    json_extract_scalar(json_column, '$.value.string_value') AS category_value
FROM json_dataset

如果JSON字符串外层是单个对象而非数组，调整清理逻辑：

WITH dataset AS (
  SELECT 
    replace(replace(json_column, '\\', ''), '"{', '{') AS cleaned_json_str
  from "thedatabase"
  LIMIT 10
),
json_dataset AS (
  SELECT cast(replace(cleaned_json_str, '}"', '}') as json) as json_column
  FROM dataset
)
SELECT
    json_extract_scalar(json_column, '$.key') AS category_key,
    json_extract_scalar(json_column, '$.value.string_value') AS category_value
FROM json_dataset

方法2：正则表达式直接提取 #

若JSON结构固定，可直接用正则从字符串中提取目标值，无需转JSON：

SELECT
    regexp_extract(json_column, '"key":"([^"]+)"') AS category_key,
    regexp_extract(json_column, '"string_value":"([^"]+)"') AS category_value
FROM "thedatabase"
LIMIT 10

该方法适合格式稳定的场景，规避转JSON时的转义问题。

方法3：使用json_parse函数（Athena引擎v3支持） #

如果你的Athena使用引擎版本3，json_parse函数对带转义的字符串处理更友好，可直接替代cast：

WITH dataset AS (
  SELECT json_parse(json_column) as json_column
  from "thedatabase"
  LIMIT 10
)
SELECT
    json_extract_scalar(json_column, '$.key') AS category_key,
    json_extract_scalar(json_column, '$.value.string_value') AS category_value
FROM dataset

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