Athena/Presto查询：筛选嵌套JSON数组含country=US的行

问题场景 #

执行查询获取嵌套数组结果：

SELECT element_at(col_name,1).entities FROM "db_name"."the_table" limit 10;

返回结果示例：

[{country=US,content=content},{country=DE,content=content},{country=GB,content=content}]
[]
[{country=HK,content=content},{country=IN,content=content},{country=GB,content=content}]
[]

尝试用any_match筛选存在country=US的行时报错“不是行”：

SELECT * FROM "db_name"."the_table" WHERE any_match(element_at(col_name,1).entities, e-> e.country= 'US') limit 10;

错误原因 #

数组中的元素是Map类型而非行（Struct）类型，无法用.语法访问字段，必须使用Map的键访问方式['key']。

正确实现方法 #

方法1：使用any_match结合Map键访问

SELECT * 
FROM "db_name"."the_table" 
WHERE any_match(element_at(col_name, 1).entities, e -> e['country'] = 'US') 
LIMIT 10;

any_match会遍历数组内的每个Map元素，判断是否存在country键值为US的元素，满足条件则返回该行。

方法2：使用transform+contains

SELECT * 
FROM "db_name"."the_table" 
WHERE contains(
    transform(element_at(col_name, 1).entities, e -> e['country']),
    'US'
)
LIMIT 10;

先通过transform提取数组中所有元素的country值生成新数组，再用contains判断新数组是否包含US，以此筛选目标行。

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