Currently I'm doing first steps with JSON in MariaDB. I have a table "users" with a column "hunde". This column is of type JSON and contains an array of IDs (which are referencing elements in a table "hunde"). The task is simple: Remove an entry in this array that contains a specific ID. I'm testing with HeidiSQL and tried this statement:
UPDATE users SET hunde=JSON_REMOVE(hunde, JSON_SEARCH(hunde, 'one', 33)) WHERE id=2
However it's throwing an error that a constraint is not fulfilled.
When I query this JSON_SEARCH(hunde, 'one', 33))
separately it returns the correct selector.
However when I query the result of the nested calls JSON_REMOVE(hunde, JSON_SEARCH(hunde, 'one', 33))
it's returning NULL.
What am I doing wrong?
I ended up with this workaround (mysqli) but it's kind of ugly and not straight forward:
$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();
Version of MariaDB is 5.5.5-10.4.17-MariaDB
I performed the query mentioned above and expected that the value is deleted but got an error.
Edit: CPUser's solution is working fine. Apparently the core is unquoting the result of the search. But I'm wondering why this is necessary: Everywhere in the examples in the docs the selector is in quotes. Why doesn't JSON_REMOVE accept this?
Try this:
UPDATE users SET hunde = JSON_REMOVE(hunde, JSON_UNQUOTE(REPLACE(JSON_SEARCH(hunde, 'one', '33', NULL, '$'), '.uid', ''))) WHERE id = 2 AND JSON_SEARCH(hunde, 'one', '33', NULL, '$') IS NOT NULL;
Im relatively new to JSON but this might work**
--OR--
Find the index of the array element you want to remove using Array.find or Array.filter or Array.forEach. Then slice it with a splice.
Essentially: You need the position of the element and then remove that index with splice.
find array,find and the rest at: How can I get the index from a JSON object with value?
Hope this helped