CODEWITHSUNDEEP
mysqlarraysjson
Sonjoy Datta
Sonjoy Datta

Reputation: 1388

JSON update single value in MySQL table

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.

Upvotes: 1

Views: 972

Answers (2)

user9628338
user9628338

Reputation:

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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.

Upvotes: 2

Related Questions