1

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.

Pikamander2
  • 7,332
  • 3
  • 48
  • 69
Sonjoy Datta
  • 1,188
  • 2
  • 13
  • 21

2 Answers2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • @Tim_Biegeleisen maybe it's not working in mysql version: 5.6.40. Am I right? – Sonjoy Datta Sep 21 '18 at 07:10
  • 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 Sep 21 '18 at 07:11
  • 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. – Tim Biegeleisen Sep 21 '18 at 07:12
  • 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. – Sonjoy Datta Sep 21 '18 at 07:23
0

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

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