Change JSON value in database with PHP

Question

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.

I'm going to add further data to this JSON data set later, so I though it's a more flexible solution than adding more and more columns later on. — sojutyp
– sojutyp, Commented Aug 17, 2021 at 10:53
Interesting read, thank you very much. For my current small project, it doesn't really matter. But for a bigger project, I'm going to use multiple columns instead. — sojutyp
– sojutyp, Commented Aug 17, 2021 at 12:02

Alexander van Oostenrijk · Accepted Answer · 2021-08-17 10:48:04Z

2

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.

answered Aug 17, 2021 at 10:48

Alexander van Oostenrijk

4,7843 gold badges25 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Change JSON value in database with PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related