如何使用AWS Athena解析嵌套JSON数组并提取指定数据

假设你已经在Athena中创建了对应JSON数据的外部表（如果还没建表，可参考下方的建表语句），通过以下SQL查询即可提取目标字段：

1. 建表语句（未创建表时使用） #

如果JSON文件存储在S3路径s3://your-bucket/path/to/json/，可以用以下语句创建外部表：

CREATE EXTERNAL TABLE IF NOT EXISTS your_table_name (
  userIdentifier STRING,
  myarray ARRAY<STRUCT<
    code: STRING,
    requiresinput: BOOLEAN,
    categorycode: STRING,
    originalrequiresinput: BOOLEAN,
    title: STRING,
    enabled: BOOLEAN
  >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket/path/to/json/';

2. 核心查询语句 #

利用UNNEST展开数组，再通过条件过滤出title等于"zz"的条目，最终提取需要的字段：

SELECT 
  userIdentifier,
  item.enabled
FROM 
  your_table_name,
  UNNEST(myarray) AS t(item)
WHERE 
  item.title = 'zz';

查询结果示例 #

useridentifier | enabled
---------------|--------
xyz-123        | true

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