How to update Json data type in MySql?

Question

I have column in my table that is Json data type. In this column I record Phones data like this:

{"0": "044-33565388", "1": "044-33565399", "2": "044-33565311"}

For store data like this form, I use textarea and enter each phone number in a new line. Next pass data to php document that parse data something like this:

$phoneList = json_encode( explode("\r\n", $input), JSON_FORCE_OBJECT)

And insert data in mysql. Now I want to update or remove some JSON data. I try something like this but got error:

Query:

UPDATE `sellers` SET `seller_phone` = JSON_SET(`seller_phone`, {"0":"33565388","1":"33565399"}) WHERE `seller_id` = 8

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"0":"33565388","1":"33565399","2":"33565311"}) WHERE seller_id = 8' at line 1

MySql version: 5.7.9

Now, What can I do to solve this problem?

Answer 1

You can use JSON_SET for update and JSON_REMOVE for unset key.

use below query to update -

UPDATE `sellers` SET `seller_phone` = JSON_SET(`seller_phone`, "$.0", "33565388", "$.1", "33565399") WHERE `seller_id` = 8;

then run below to unset last key -

UPDATE `sellers`
SET `seller_phone` = JSON_REMOVE(`seller_phone`, "$.2") WHERE `seller_id` = 8

Answer 2

Firstly you need to understand the format for JSON_SET

JSON_SET(column_name, path, val[, path, val]...)

To set values

JSON_SET(`seller_phone`, "$.0", "33565388", "$.1", "33565399") 

So your Query will be:

UPDATE `sellers`
SET `seller_phone` = JSON_SET(`seller_phone`, "$.0", "33565388", "$.1", "33565399")
WHERE `seller_id` = 8