MariaDB中移除JSON数组指定值元素的问题求助

问题描述 #

刚接触MariaDB的JSON操作，现有users表，其中hunde列为JSON类型，存储关联hunde表的ID数组。需求是删除数组中指定ID的元素，使用HeidiSQL测试以下语句：

UPDATE users SET hunde=JSON_REMOVE(hunde, JSON_SEARCH(hunde, 'one', 33)) WHERE id=2

但报错提示约束未满足。单独执行JSON_SEARCH(hunde, 'one', 33)能返回正确的选择器，但嵌套调用JSON_REMOVE(hunde, JSON_SEARCH(hunde, 'one', 33))却返回NULL。已用以下mysqli代码临时解决，但不够简洁：

$dbAktionUser = $db->prepare("SELECT JSON_SEARCH(hunde, 'one', ?) AS item FROM users WHERE id=?");
$dbAktionUser->bind_param('ii', $idHund, $idUser);
$dbAktionUser->execute();
$resultQuery = $dbAktionUser->get_result();
while ($row = $resultQuery->fetch_object()) {
    $item = $row->item;
}
var_dump($item);
$dbAktionUser = $db->prepare("UPDATE users SET hunde=JSON_REMOVE(hunde, $item) WHERE id=?");
$dbAktionUser->bind_param('i', $idUser);
$res = $dbAktionUser->execute();

使用的MariaDB版本为5.5.5-10.4.17-MariaDB，请问问题出在哪里？

问题原因及解决办法 #

核心原因 #

问题源于MariaDB 10.4中JSON_SEARCH返回的路径字符串自带双引号，而JSON_REMOVE要求传入的是不带引号的合法JSON路径。直接嵌套调用时，JSON_REMOVE会把带引号的字符串识别为无效路径，最终返回NULL；如果hunde列设置了NOT NULL约束，就会触发"约束未满足"的报错。

比如单独执行JSON_SEARCH(hunde, 'one', 33)会返回类似"$[0]"的带引号结果，直接传给JSON_REMOVE相当于执行JSON_REMOVE(hunde, '"$[0]"')，这显然不是合法的路径格式，因此无法正确执行。

正确解决方法 #

在JSON_SEARCH外层包裹JSON_UNQUOTE()，去掉路径字符串的引号，让JSON_REMOVE能正确识别路径：

UPDATE users 
SET hunde = JSON_REMOVE(hunde, JSON_UNQUOTE(JSON_SEARCH(hunde, 'one', 33))) 
WHERE id = 2;

临时方案有效的原因 #

你的PHP临时方案中，先通过SELECT取出带引号的路径（比如"$[0]"），然后直接拼接到UPDATE语句里——此时SQL语句实际变成了JSON_REMOVE(hunde, "$[0]")，SQL中的双引号被当作字符串边界，实际传入JSON_REMOVE的是不带引号的$[0]，因此能正常执行。但这种拼接方式存在SQL注入风险，建议改用上面的单条SQL语句，既简洁又安全。

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