如何通过SQL从Scival REST API的JSON数据提取最新年份的valueByYear值

原SQL的问题 #

1. valueByYear是JSON对象而非数组：你尝试用$.valueByYear[1]这种数组索引访问方式完全错误，JSON对象的属性没有下标，必须通过遍历键值对的方式提取数据。
2. 直接提取valueByYear为数值类型报错：$.valueByYear是包含多个键值对的对象，不是单个数值，无法直接映射到NUMBER类型。

解决思路 #

要获取最新年份对应的数值，需要：

1. 提取作者信息与metrics基础数据
2. 将valueByYear的键值对拆分为单独的行（年份+对应数值）
3. 对年份进行降序排序，筛选出每个作者+指标类型对应的最新年份数据

完整可行SQL #

SELECT
    author_id,
    author_name,
    metric_type,
    latest_year,
    latest_value
FROM (
    SELECT
        a.id AS author_id,
        a.name AS author_name,
        m.metric_type,
        TO_NUMBER(y.year) AS latest_year,
        y.value AS latest_value,
        -- 按作者和指标分组，给年份降序排名，排名1即为最新年份
        ROW_NUMBER() OVER (PARTITION BY a.id, m.metric_type ORDER BY TO_NUMBER(y.year) DESC) AS rn
    FROM
        your_table d, -- 替换为你的实际表名
        -- 提取作者信息
        JSON_TABLE(d.json_column FORMAT JSON, '$.author' COLUMNS (
            id VARCHAR2(20) PATH '$.id',
            name VARCHAR2(100) PATH '$.name'
        )) a,
        -- 遍历metrics数组，同时拆分valueByYear的键值对
        JSON_TABLE(d.json_column FORMAT JSON, '$.metrics[*]' COLUMNS (
            metric_type VARCHAR2(50) PATH '$.metricType',
            -- 嵌套遍历valueByYear的所有属性，$key获取年份字符串，$获取对应数值
            NESTED PATH '$.valueByYear.*' COLUMNS (
                year VARCHAR2(4) PATH '$key',
                value NUMBER PATH '$'
            )
        )) m
) ranked_data
WHERE rn = 1;

关键说明 #

• NESTED PATH '$.valueByYear.*'：用于遍历JSON对象的所有属性，$key返回属性名（即年份字符串），$返回属性值（即对应指标数值）。
• TO_NUMBER(y.year)：将年份字符串转为数值类型，确保排序逻辑正确（避免字符串排序的异常，比如"1000"字符串会排在"999"之后，但数值排序是正确的）。
• ROW_NUMBER() OVER(...)：按作者ID和指标类型分组，对年份降序排名，筛选排名为1的行即可得到最新年份的数据。

简化版（仅提取最新年份数值） #

如果不需要所有年份的中间数据，也可以用JSON函数直接计算，但兼容性不如拆分行的方式：

SELECT
    a.id AS author_id,
    a.name AS author_name,
    m.metric_type,
    -- 提取最大年份对应的数值
    JSON_VALUE(d.json_column, '$.metrics[0].valueByYear.' || MAX(TO_NUMBER(y.year))) AS latest_value,
    MAX(TO_NUMBER(y.year)) AS latest_year
FROM
    your_table d,
    JSON_TABLE(d.json_column FORMAT JSON, '$.author' COLUMNS (
        id VARCHAR2(20) PATH '$.id',
        name VARCHAR2(100) PATH '$.name'
    )) a,
    JSON_TABLE(d.json_column FORMAT JSON, '$.metrics[*]' COLUMNS (
        metric_type VARCHAR2(50) PATH '$.metricType',
        NESTED PATH '$.valueByYear.*' COLUMNS (
            year VARCHAR2(4) PATH '$key'
        )
    )) m
GROUP BY a.id, a.name, m.metric_type;

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