If I have a MySQL table with a JSON column called numbers and a record that has [1, 2, 3] in that column (array of integers), how do I update that record to remove the 2 (so it becomes [1, 3])?
-
Do you want to do that using query or using any scripting language like PHP or PythonRode093– Rode0932016-11-08 23:04:38 +00:00Commented Nov 8, 2016 at 23:04
-
Doing this with code is not a problem; I'm looking for an sql-only solution as the set is huge.John Derring– John Derring2016-11-08 23:08:40 +00:00Commented Nov 8, 2016 at 23:08
Add a comment
|
3 Answers
I was searching for an answer my self and came to this question, not wanting to use objects I continued the search. But I found a solution, you need to use a combination of json_remove and json_search
The following removes the value 1 from the table tbl and the column numbers
UPDATE tbl
SET numbers = JSON_REMOVE(
numbers, replace(json_search(numbers, 'one', 1), '"', '')
)
WHERE json_search(numbers, 'one', 1) IS NOT NULL
json_searchreturns the path of where the value is, ie."$[0]"replaceremove the"otherwise an error will occur withjson_removejson_removewill remove the path from thejson_searchresult
Et voila, your value is removed.
Note: this assumes no duplicate values
9 Comments
John Derring
Nice. Thanks for contributing.
Karim
Keep in mind that JSON_SEARCH won't find integer number if in numbers you store integer values (bugs.mysql.com/bug.php?id=79316).
Nilo Araujo
You can use JSON_UNQUOTE instead of replace dev.mysql.com/doc/refman/5.7/en/…
MKroeders
@amaster don't know a way directly, after a bit of testing. I think creating your own function using repeat (mysqltutorial.org/mysql-stored-procedure/mysql-repeat-loop) and then looping over it until no key is found is your safest bet
Gaurav Agrawal
This answer is not working and it's wrong. json_search is not working with integers.
|
Until someone finds a better solution, I just converted it to an object instead: {"1": 1, "2": 2, "3": 3}. Yes, this is uglier and occupies more disk space, but you get the benefit of not having to worry about duplicates.
To add a number:
update tbl set numbers = json_insert(`numbers`, '$."4"', 4);
To remove a number:
update tbl set numbers = json_remove(`numbers`, '$."4"');
To get the row with a certain number:
select * from tbl where json_contains_path(`numbers`, 'one', '$."4"');
Comments
By the way,
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path]
...])
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-search
just search_str
