Json data update in MySQL

Databases
, , ,
#1

I am saving the data in my database like {"name":"User Name","age":"32","gender":"M"} I have concern when I am updating this data I want to add some more data with this JSON like "city":"City_Name","state":"State_Name".
so after update the final data will be like this {"name":"User Name","age":"32","gender":"M","city":"City_Name","state":"State_Name"}

-----------------------------------------------------------------------
| ID  |  col_name                                                            
-----------------------------------------------------------------------
| 1   |  {"name":"User Name","age":"32","gender":"M"}

I can able to update existing data in JSON but don’t know How I add new data in JSON with an update query in phpMyAdmin.

Please help How me achieve the goal.
for updating, I am using the code below.


...
let obj = JSON.parse(stmt[0].col_name);

const stmt2 = await con.query("UPDATE `table` SET `col_name` = :data WHERE `col_id` = 1, 
{ 
 replacements: { data: JSON.stringify(obj) }, 
 type: con.QueryTypes.UPDATE
});
#2

Why are you storing the data in JSON?

#3

to create fewer columns and I already made this and I have a buddle of logs in the database so there is no change to rollback :sob:

#4

There are valid reasons one may want to store JSON data, none of which you have mentioned.

In a properly Normalized DB the column count is not anything you would be concerned with.

Not a good reason to continue on a path you probably should not be on.

Not really sure what this means.

Nevertheless, if you insist on continuing down this road you will need to learn the JSON DB commands. You are trying to use the RDMS command (SET).

JSON_SET and the free manual is your friend here.
https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set

JSON_SET() replaces existing values and adds nonexisting values.

If you are going to stick with the JSON data store you would probably be better off using a purpose built NoSql DB like MongoDB