I have a table which has a JSON column called people like this:

...and I need to update the people column and put a 0 in the path $[*].id where id = 3, so after executing the query, the table should end like this:

There may be more than one match per row.

Honestly, I didn´t tried any query since I cannot figure out how can I loop inside a field, but my idea was something like this:

UPDATE mytable
    SET people = JSON_SET(people, '$[*].id', 0)

WHERE /* ...something should go here */

This is my version

SELECT VERSION()

 ----------------- 
|    version()    |
 ----------------- 
| 10.4.22-MariaDB |
 ----------------- 

CodePudding user response：

If the id values in people are unique, you can use a combination of JSON_SEARCH and JSON_REPLACE to change the values:

UPDATE mytable
SET people = JSON_REPLACE(people, JSON_UNQUOTE(JSON_SEARCH(people, 'one', 3)), 0)
WHERE JSON_SEARCH(people, 'one', 3) IS NOT NULL

Note that the WHERE clause is necessary to prevent the query replacing values with NULL when the value is not found due to JSON_SEARCH returning NULL (which then causes JSON_REPLACE to return NULL as well).

If the id values are not unique, you will have to rely on string replacement, preferably using REGEXP_REPLACE to deal with possible differences in spacing in the values (and also avoiding replacing 3 in (for example) 23 or 34:

UPDATE mytable
SET people = REGEXP_REPLACE(people, '("id"\\s*:\\s*)2\\b', '\\14')

Demo on dbfiddle

CodePudding user response：

As stated in the official documentation, MySQL stores JSON-format strings in a string column, for this reason you can either use the JSON_SET function or any string function.

For your specific task, applying the REPLACE string function may suit your case:

UPDATE 
    mytable
SET 
    people = REPLACE(people, 3, 0)
WHERE 
    ....;