Athena字符串型嵌套JSON UNNEST报错如何解决以适配Quicksight展示

问题原因 #

你当前detail字段为字符串（varchar）类型，UNNEST函数仅支持对数组/集合类型执行展开操作，直接对字符串执行UNNEST会触发你遇到的参数类型不匹配错误。

解决方案 #

你需要先用from_json函数将JSON格式的detail字符串反序列化为对应的结构化类型，再逐层展开数组即可，示例查询如下：

SELECT
  source,
  account,
  resource.id AS resource_id,
  resource.type AS resource_type
FROM (
  -- 将字符串类型的detail转换为对应结构的struct
  SELECT
    source,
    account,
    from_json(detail, 'struct<findings:array<struct<productarn:string,resources:array<struct<partition:string,type:string,region:string,id:string>>>>>') AS detail_struct
  FROM testfindings
)
-- 展开findings数组
CROSS JOIN UNNEST(detail_struct.findings) AS t(finding)
-- 展开每个finding下的resources数组
CROSS JOIN UNNEST(finding.resources) AS t(resource)

后续适配Quicksight的操作建议 #

• 可以直接将上述查询作为自定义SQL，在Quicksight中创建数据集，返回的source、account、resource_id、resource_type均为基础字符串类型，Quicksight可以正常识别和使用
• 数据量较大的场景下，建议在Athena中执行CREATE TABLE testfindings_flat AS [上述查询语句]生成一张新的平面表，后续直接读取该表即可，查询性能更高。

如果执行转换时报错，可先检查detail字段的JSON格式是否合法，是否存在多余转义字符。

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