Search code examples
jsonmariadb

JSON in MariaDb: Removing element in array with specific value

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?

Solution

  • 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