在Athena中从JSON数组提取狗狗名字数组的SQL查询方案

现有数据中包含名为pets的JSON数组变量（数据类型为ROW），示例数据如下：

pets
---
[{"type":"dog", "name":"fido"}, {"type":"cat", "name":"steve"}, {"type":"dog", "name":"carl"}]
[{"type":"dog", "name":"ellie"}, {"type":"fish", "name":"goldie"}]
[{"type":"cat", "name":"simone"}, {"type":"lizard", "name":"sparky"}]

需要编写SQL查询，得到每行对应的狗狗名字数组，期望输出格式：

dog_names
---
["fido", "carl"]
["ellie"]
[]

目前已能通过filter(pets, r -> r.type= 'dog')过滤出狗狗条目，但无法批量提取名字，且无狗狗时需避免报错。

可以结合filter和transform函数实现需求，SQL语句如下：

transform(filter(pets, r -> r.type = 'dog'), r -> r.name) AS dog_names

说明 #

• filter(pets, r -> r.type = 'dog')：先从pets数组中筛选出所有类型为dog的元素，得到仅包含狗狗条目的子数组；
• transform(..., r -> r.name)：遍历过滤后的子数组，提取每个元素的name字段值，最终生成由狗狗名字组成的数组；
• 当没有狗狗时，filter返回空数组，transform处理空数组会直接返回空数组[]，不会触发报错，完全满足需求。

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