MariaDB 10.3.29无法更新JSON列内匹配指定条件的属性怎么办

问题原因 #

你的investments列存储的是JSON数组，而非单个JSON对象，原SQL中直接使用$.investor、$.paid的路径没有指定数组元素下标，无法匹配到目标属性，所以更新不生效。

可行解决方法 #

方案1：MySQL 8.0+ 推荐（支持批量更新同数组内多个匹配项）

通过JSON_TABLE将JSON数组转为临时关系表，定位到匹配元素的下标后更新：

UPDATE purchased_vehicles pv
JOIN JSON_TABLE(
    pv.investments,
    '$[*]' COLUMNS (
        arr_idx FOR ORDINALITY,
        target_investor INT PATH '$.investor'
    )
) jt ON jt.target_investor = 1
SET pv.investments = JSON_SET(
    pv.investments,
    CONCAT('$[', jt.arr_idx - 1, '].paid'),
    450
);

注：FOR ORDINALITY返回的下标从1开始，JSON数组下标从0开始，所以需要减1做对齐。

方案2：兼容MySQL 5.7+ （仅更新第一个匹配项）

通过JSON_SEARCH定位目标属性路径后替换更新：

UPDATE purchased_vehicles
SET investments = JSON_SET(
    investments,
    REPLACE(
        JSON_UNQUOTE(JSON_SEARCH(investments, 'one', 1, NULL, '$[*].investor')),
        '.investor',
        '.paid'
    ),
    450
)
WHERE JSON_CONTAINS(investments, '{"investor": 1}', '$');

注：如果需要更新同数组内所有匹配的investor=1的项，需要把JSON_SEARCH的one参数改为all，再配合循环逻辑处理即可。

注意事项 #

• 如果paid字段存储为数值类型，不需要给450加单引号，避免写入字符串类型的值
• 更新前建议先执行SELECT验证匹配结果，避免误改数据

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