我在 Athena 查詢下方撰寫了用于在 Quicksight 中顯示/可視化資料的內容。只要我在表中有一個記錄，這個作業就很好。如果您看到我已將查詢硬編碼為索引 1。我如何修改以下查詢以包含表中的所有資料。如果洗掉索引 1 那么它會給我錯誤
SYNTAX_ERROR: line 8:19: Expression result.extensions.response is not of type ROW
詢問：
select user_id, assessment_id, created_by,result.extensions.response[1].assessmentid AS AssesmentId,
result.extensions.response[1].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
CROSS JOIN UNNEST(result.extensions.response[1].responseData) AS t(response_json)
輸出：
雅典娜表 DDL：
CREATE EXTERNAL TABLE `focalbucket`(
`assessment_id` int COMMENT 'from deserializer',
`id` string COMMENT 'from deserializer',
`user_id` string COMMENT 'from deserializer',
`project_id` int COMMENT 'from deserializer',
`created_by` string COMMENT 'from deserializer',
`team_id` int COMMENT 'from deserializer',
`result` struct<extensions:struct<response:array<struct<assessmentid:int,assessmentname:string,assessmentcreateddate:string, responsedata:array<struct<questionid:int,answers:array<struct<answerid:int,answertext:string>>,
questiontext:string,questiontype:string>>,userfullname:string>>>,
completion:boolean,platform:string,app_version:string> COMMENT 'from deserializer',
`verb` struct<id:string> COMMENT 'from deserializer',
`actor` struct<mbox:string,name:string> COMMENT 'from deserializer',
`timestamp` bigint COMMENT 'from deserializer',
`version` string COMMENT 'from deserializer')
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
uj5u.com熱心網友回復：
result.extensions.response也是一個陣列，因此您也可以取消嵌套。像這樣的東西（注意 - 未測驗，因為沒有提供示例資料）：
select
user_id,
assessment_id,
created_by,
t.response.assessmentid AS AssesmentId,
t.response.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
CROSS JOIN UNNEST(result.extensions.response) AS t(response)
CROSS JOIN UNNEST(t.response.responseData) AS tt(response_json)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/363537.html
上一篇：有沒有辦法使用ROW_NUMBER()并使用相同的識別符號保留編號？
下一篇：一次處理多個存盤程序狀態