AWS Athena中JSON数组/Null转字符串报错问题求助

问题背景 #

现有两种格式的JSON数据：

• 数组格式：myjson = {"key":["value1","value2"]}
• Null格式：myjson = {"key":null}

尝试用cast(json_extract(myjson,'$.key') as varchar)转换时报错，原因是JSON数组对象无法直接转换为VARCHAR类型，需要针对性处理数组和Null两种场景，最终输出value1, value2或null格式的字符串。

解决方案 #

根据不同SQL引擎，提供以下实现方式：

1. Spark SQL 实现 #

SELECT 
  CASE 
    WHEN json_extract(myjson, '$.key') IS NULL THEN 'null'
    ELSE array_join(from_json(json_extract(myjson, '$.key'), 'array<string>'), ', ')
  END AS key_str
FROM your_table;

• 逻辑：先用from_json将JSON数组转为SQL数组类型，再用array_join拼接成逗号分隔的字符串；Null场景直接返回'null'。

2. Hive SQL 实现（Hive 2.3+） #

SELECT 
  CASE 
    WHEN get_json_object(myjson, '$.key') IS NULL THEN 'null'
    ELSE array_join(from_json(get_json_object(myjson, '$.key'), 'array<string>'), ', ')
  END AS key_str
FROM your_table;

• 逻辑：用get_json_object提取JSON值，from_json转数组后用array_join拼接，Null分支返回目标字符串。

3. MySQL 实现 #

SELECT 
  COALESCE(
    (SELECT GROUP_CONCAT(value SEPARATOR ', ') 
     FROM JSON_TABLE(myjson->'$.key', '$[*]' COLUMNS(value VARCHAR(255) PATH '$')) AS jt),
    'null'
  ) AS key_str
FROM your_table;

• 逻辑：用JSON_TABLE将JSON数组展开为行数据，GROUP_CONCAT拼接成字符串；COALESCE捕获Null场景，返回'null'。

核心原理 #

原方法报错是因为json_extract返回的是JSON对象类型（数组或Null），而非原生字符串。需要先将JSON数组转换为SQL原生数组/行集，再通过字符串拼接函数生成目标格式；同时通过条件判断处理Null场景，确保输出符合预期。

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