JSON in MariaDb: Removing element in array with specific value

Question

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.

I wonder if you're hitting a bug fixed in higher versions? ... I could not find an online fiddle that has a version listed in the Fix Version/s list at that link. — Paul T.
– Paul T., Commented Sep 2, 2024 at 23:26
Provide a sample according to Tips for asking a good Structured Query Language (SQL) question, #5 and #3. — Akina
– Akina, Commented Sep 3, 2024 at 4:21

Nimantha · Accepted Answer · 2025-08-09 10:10:44Z

0

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;

--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?

edited Aug 9 at 10:10

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Sep 3, 2024 at 4:41

CPUser

164 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CPUser Over a year ago

Finding index example: developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/…

CPUser Over a year ago

Otherwise use: 1. Find the path of the element: Use JSON_SEARCH() to locate the element within the array. This function returns the path to the element. Remove the element: Use JSON_REMOVE() to remove the element at the specified path.

Collectives™ on Stack Overflow

JSON in MariaDb: Removing element in array with specific value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related