如何从MySQL JSON字段提取数组值，路径不存在时返回null或默认值

直接使用带通配符*的JSON_EXTRACT无法实现你要的效果，因为通配符路径求值时会自动跳过不存在对应路径的元素，不会返回null占位。可以通过以下两种方案实现需求：

方案1（MySQL 8.0+ 推荐）：用JSON_TABLE拆分数组再聚合 #

JSON_TABLE可以把JSON数组拆分为多行数据，每行对应数组中的一个元素，单独对每个元素提取路径时，不存在的路径会自动返回null，最后再用JSON_ARRAYAGG把结果聚合为数组即可，顺序和原数组完全一致。
示例代码如下，假设你的表名为your_table，存储JSON的列名为json_column：

SELECT JSON_ARRAYAGG(
    JSON_EXTRACT(item, '$.details.value')
) AS expected_result
FROM your_table,
JSON_TABLE(
    json_column->'$.items',
    '$[*]' COLUMNS (
        item JSON PATH '$'
    )
) AS split_items

执行上述语句得到的结果就是你期望的[1, 2, null, 4]。

方案2（低版本MySQL适配）：按索引手动提取 #

如果你的MySQL版本低于8.0不支持JSON_TABLE，且提前知道items数组的最大长度，可以手动按索引逐个提取值再拼接为数组：

SELECT JSON_ARRAY(
    JSON_EXTRACT(json_column, '$.items[0].details.value'),
    JSON_EXTRACT(json_column, '$.items[1].details.value'),
    JSON_EXTRACT(json_column, '$.items[2].details.value'),
    JSON_EXTRACT(json_column, '$.items[3].details.value')
) AS expected_result
FROM your_table

该方案仅适合数组长度固定或最大长度可控的场景，灵活度低于方案1。

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