Athena查询employee表JoinedGeneratedTimestamp字段返回空值求助

问题原因 #

你的employee_detail列存储的是类JSON格式，并非标准JSON：

• 使用了单引号（标准JSON要求键和字符串值必须用双引号）
• 用Python风格的None代替了JSON规范的null

仅替换单引号无法让Athena正确解析JSON结构，导致json_extract_scalar返回空值。

解决方案 #

先将数据转换为标准JSON格式，再提取目标字段：

正确SQL示例 #

WITH data AS (
  SELECT 
    employee_detail,
    -- 先替换单引号为双引号，再替换None为null，转换为标准JSON
    json_parse(replace(replace(employee_detail, '''', '"'), 'None', 'null')) AS parsed_json
  FROM "employee" 
  LIMIT 5
)
SELECT 
  employee_detail,
  -- 从解析后的JSON中提取目标字段
  json_extract_scalar(parsed_json, '$.empdetail.JoiningDetail.JoinedGeneratedTimestamp') AS JoinedGeneratedTimestamp,
  json_extract_scalar(parsed_json, '$.empdetail.JoiningDetail.position') AS position
FROM data;

简化写法（合并替换逻辑） #

SELECT 
  employee_detail,
  json_extract_scalar(
    json_parse(replace(replace(employee_detail, '''', '"'), 'None', 'null')),
    '$.empdetail.JoiningDetail.JoinedGeneratedTimestamp'
  ) AS JoinedGeneratedTimestamp,
  json_extract_scalar(
    json_parse(replace(replace(employee_detail, '''', '"'), 'None', 'null')),
    '$.empdetail.JoiningDetail.position'
  ) AS position
FROM "employee" 
LIMIT 5;

关键说明 #

• json_parse函数必须传入标准JSON字符串，因此必须先处理单引号和None这两个非标准格式。
• 如果JoinedGeneratedTimestamp是数值类型，也可以用json_extract替代json_extract_scalar，再通过cast(...) as bigint转换为对应数值类型。

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