如何在SQL数据库中提取JSON对象里最新日期对应的值？

根据你提供的表结构和需求，以下是主流数据库的实现方案，核心思路是将JSON对象的键值对展开为行数据，过滤空值后筛选出最新日期对应的数值：

1. MySQL 8.0+ #

方法一：使用JSON_TABLE展开后聚合 #

SELECT 
    t.id,
    t.series,
    MAX(j.value) KEEP (DENSE_RANK LAST ORDER BY j.date_str) AS value
FROM 
    your_table t,
    JSON_TABLE(
        JSON_KEYS(t.series),
        '$[*]' COLUMNS(
            date_str VARCHAR(30) PATH '$',
            value DOUBLE PATH CONCAT('$.', JSON_UNQUOTE('$'))
        )
    ) j
WHERE 
    j.value IS NOT NULL
GROUP BY 
    t.id, t.series;

方法二：先找最大日期键再提取值 #

SELECT
    id,
    series,
    JSON_UNQUOTE(JSON_EXTRACT(series, CONCAT('$.', MAX(date_str)))) AS value
FROM (
    SELECT
        id,
        series,
        JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(series), CONCAT('$[', idx, ']'))) AS date_str
    FROM your_table,
    JSON_TABLE(
        JSON_KEYS(series),
        '$[*]' COLUMNS(idx FOR ORDINALITY, date_str VARCHAR(30) PATH '$')
    ) keys
    WHERE JSON_EXTRACT(series, CONCAT('$.', date_str)) IS NOT NULL
) sub
GROUP BY id, series;

2. PostgreSQL #

方法一：子查询直接取最新值 #

SELECT
    t.id,
    t.series,
    (SELECT value::numeric FROM json_each_text(t.series) j WHERE j.value IS NOT NULL ORDER BY j.key DESC LIMIT 1) AS value
FROM your_table t;

方法二：聚合筛选 #

SELECT
    t.id,
    t.series,
    MAX(j.value::numeric) FILTER (WHERE j.key = MAX(j.key)) AS value
FROM your_table t,
LATERAL json_each_text(t.series) j
WHERE j.value IS NOT NULL
GROUP BY t.id, t.series;

3. SQL Server #

SELECT
    t.id,
    t.series,
    j.value AS value
FROM your_table t
CROSS APPLY (
    SELECT TOP 1 value
    FROM OPENJSON(t.series)
    WHERE value IS NOT NULL
    ORDER BY CONVERT(datetime2, [key]) DESC
) j;

说明 #

• 请将your_table替换为你的实际表名
• 过滤value IS NOT NULL是为了排除像2016-01-31T00:00:00.000Z这种无有效值的条目
• ISO格式的日期字符串可直接按字典序排序（与时间序一致），部分数据库也可转换为日期类型后排序更严谨

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