如何在AWS Athena中选取JSON数组的最后一个元素？

假设数据库中有如下包含JSON数组的数据集：

WITH dataset AS (
    SELECT *
    FROM (VALUES 
        ('1', JSON'[{ "name" : "foo" }, { "name" : "bar" }]'),
        ('2', JSON'[{ "name" : "fizz" }, { "name" : "buzz" }]'),
        ('3', JSON'[{ "name" : "hello" }, { "name" : "world" }]')
    ) AS t(id, my_array)
)

需要提取每个JSON数组最后一个元素的name属性，期望结果如下：

目前可以轻松提取第一个元素：

SELECT json_extract_scalar(my_array, '$[0].name') FROM dataset

但以下几种尝试均无法获取最后一个元素：

-- 负索引方式无效
SELECT json_extract_scalar(my_array, '$[-1].name') FROM dataset

-- 尝试用cardinality计算索引无效
SELECT json_extract_scalar(my_array, '$[cardinality(json_parse(my_array)) - 1].name') FROM dataset

-- element_at函数无法直接作用于JSON类型
SELECT element_at(my_array, -1) FROM dataset

注意：无法预先假设JSON数组的长度。

解决方案 #

根据不同SQL方言，提供几种通用可行的方法：

方法1：转原生数组后取最后元素

先把JSON数组解析成SQL原生数组，再用element_at获取最后一个元素，最后提取name属性：

SELECT json_extract_scalar(element_at(json_parse(my_array), -1), '$.name') 
FROM dataset;

如果你的SQL方言支持数组负索引，也可以简化为：

SELECT json_extract_scalar(json_parse(my_array)[-1], '$.name') 
FROM dataset;

方法2：用JSON路径last()函数（部分方言支持）

如果数据库支持JSON路径的last()函数（比如PostgreSQL 12+），可以直接用JSON路径查询：

SELECT json_path_query_scalar(my_array, '$[last()].name') 
FROM dataset;

方法3：展开数组后取最后一条（兼容多数方言）

如果上述方法都不支持，可以通过unnest展开数组，再用窗口函数标记最后一条：

SELECT result
FROM (
    SELECT 
        json_extract_scalar(elem, '$.name') AS result,
        row_number() OVER (PARTITION BY id ORDER BY ordinality DESC) AS rn
    FROM dataset,
         unnest(json_parse(my_array)) WITH ORDINALITY AS elem
) t
WHERE rn = 1;

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