使用JSON_EXTRACT提取JSON时如何让不存在的属性返回NULL值

核心原因：直接使用JSON_EXTRACT配合[*]通配符提取属性时，默认会自动过滤不存在目标属性的元素，所以不会返回对应的NULL。需要先展开JSON数组，逐个提取属性后再重新聚合为数组，就能保留缺失属性对应的NULL值。

MySQL 8.0+ 实现示例 #

SELECT JSON_ARRAYAGG(
    JSON_UNQUOTE(JSON_EXTRACT(day_item, '$.entries[0].startTimeDelta'))
) AS list
FROM table,
-- 展开days数组为单独的行
JSON_TABLE(
    column->'$.days',
    '$[*]' COLUMNS (
        day_item JSON PATH '$'
    )
) AS days_table
GROUP BY table.主键列; -- 按表的主键分组，保证每行原数据对应一个聚合后的数组

如果你的JSON数组长度固定，也可以按索引手动提取拼接：

SELECT JSON_ARRAY(
    JSON_UNQUOTE(JSON_EXTRACT(column, '$.days[0].entries[0].startTimeDelta')),
    JSON_UNQUOTE(JSON_EXTRACT(column, '$.days[1].entries[0].startTimeDelta')),
    JSON_UNQUOTE(JSON_EXTRACT(column, '$.days[2].entries[0].startTimeDelta'))
) AS list
FROM table;

PostgreSQL 实现示例 #

SELECT json_agg(
    (day_item->'entries'->0->>'startTimeDelta')::text
) AS list
FROM table,
jsonb_array_elements(column->'days') AS days(day_item)
GROUP BY table.主键列;

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