如何修改Athena查询解析嵌套JSON以适配Quicksight全量数据可视化

问题原因 #

根据你提供的DDL定义，result.extensions.response是数组(Array)类型，你直接移除下标后相当于尝试从数组直接读取ROW字段，自然触发类型不匹配的语法报错。要读取全量数据，需要先把外层的response数组展开，再处理内层的responsedata数组。

修改后的查询语句 #

SELECT 
  user_id, 
  assessment_id, 
  created_by,
  resp.assessmentid AS AssesmentId,
  resp.assessmentname AS AssesmentName,
  response_json.questionid AS QuestionId,
  response_json.questiontext as Questiontext,
  transform(response_json.answers, answer -> answer.answerid) AS AnswerID,
  transform(response_json.answers, answer -> answer.answertext) AS AnswerText
FROM focalbucket
-- 先展开外层的response数组，得到每个response元素的单行结构resp
CROSS JOIN UNNEST(result.extensions.response) AS t(resp)
-- 再展开每个resp里的responsedata数组
CROSS JOIN UNNEST(resp.responsedata) AS t2(response_json)

注意事项 #

• 字段名大小写要和DDL定义对齐：DDL里定义的是responsedata、answerid、answertext全小写，如果你原JSON里的字段是驼峰写法，建表时如果没有开启大小写匹配可以调整字段名对应即可。
• 如果需要保留response数组为空的记录，可以把CROSS JOIN换成LEFT JOIN，后面加ON true即可。

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