JSON update single value in MySQL table

Question

I have a JSON array in the MySQL payment table details column. I need to update a single value of this JSON array. What is the procedure to update JSON using MySQL?

JSON Array

{"items":[{"ca_id":18,"appointment_date":"2018-09-15 15:00:00","service_name":"Software Installation / Up-gradation","service_price":165}],"coupon":{"code":"GSSPECIAL","discount":"10","deduction":"0.00"},"subtotal":{"price":165,"deposit":0},"tax_in_price":"included","adjustments":[{"reason":"Over-time","amount":"20","tax":"0"}]}

I need to update the appointment _date 2018-09-15 15:00:00 to 2018-09-28 15:00:00.

what server side language are you using?

user9628338
– user9628338

2018-09-21 06:44:55 +00:00
Commented Sep 21, 2018 at 6:44 — user9628338
– user9628338, Commented Sep 21, 2018 at 6:44
Oh sorry, I'm using PHP.

Sonjoy Datta
– Sonjoy Datta

2018-09-21 06:46:38 +00:00
Commented Sep 21, 2018 at 6:46 — Sonjoy Datta
– Sonjoy Datta, Commented Sep 21, 2018 at 6:46

Tim Biegeleisen · Accepted Answer · 2018-09-21 06:53:50Z

2

Here is a pure MySQL JSON way of doing this:

UPDATE yourTable
SET col = JSON_REPLACE(col, '$.items[0].appointment_date', '2018-09-28 15:00:00');

The best I could come up with is to address the first element of the JSON array called items, and then update the appointment_date field in that array element.

Here is a demo showing that the JSON replacement syntax/logic is working:

Demo

But, you could equally as well have done this JSON work in your PHP layer. It might make more sense to do this in PHP.

answered Sep 21, 2018 at 6:53

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

Sonjoy Datta Over a year ago

@Tim_Biegeleisen maybe it's not working in mysql version: 5.6.40. Am I right?

Tim Biegeleisen Over a year ago

I didn't know your version, but assumed your version had support for at least the basic JSON functions. You might have no other choice then but to do this in PHP (not the the worst thing at all however). I will leave this answer up as a valid option for those with a newer version of MySQL.

Tim Biegeleisen Over a year ago

One more thing: Consider upgrading to the latest MySQL 8+ which, in addition to having JSON support, also supports many analytic functions previously not available to MySQL.

Sonjoy Datta Over a year ago

My version is 5.6.40. I have found an documentation in the GitHub, where I can see the JSON_REPLACE function has started from version 5.7. Thank you man.

score 0 · Accepted Answer · 2018-09-21 06:55:20Z

0

If you want to do this in php then, steps to follow:

Select the respective column from the table
Use json_decode to convert the string to array
Now you have the json object, apply your modifications
Use json_encode to convert your json object back to string
Save this string in table

edited Sep 21, 2018 at 6:55

answered Sep 21, 2018 at 6:49

user9628338

Collectives™ on Stack Overflow

JSON update single value in MySQL table

2 Answers 2

Demo

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related