Skip to content
Advertisement

Change JSON value in database with PHP

I have a params column in my MySQL database containing this: '{"usergroup" : "1", "language" : "ENG"}'; . Can I change only the language value ENG to GER in just 1 step using a standard PDO update?

Obviously, I could just SELECT the whole content of my table cell '{"usergroup" : "1", "language" : "ENG"}'; , decode it with json_decode, change ENG to GER and UPDATE the table cell in a second step.

I’m looking for a shortcut that allows me to UPDATE only ENG to GER without touching the other data or decode/encode the whole data set.

Advertisement

Answer

You can use the JSON_REPLACE function for this.

UPDATE `MyTable`
SET `json` = JSON_REPLACE(json, '$.language', 'GER')
WHERE ...

Here is an overview of other JSON manipulation functions: https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement