I have the following json field
{
"Covid-19Vaccine Staus": "Not vaccinated (intent to in the future)",
"Date of last vaccine taken": "2021-08-09T00:00:00+04:00",
"If vaccinated, Name of vaccination received": "Other WHO Approved vaccine"
}
What i would like to do is update the key description i.e. Covid-19 Vaccine Staus to Covid19VaccineStaus.
On doing a direct update to the field on mysql workbench it generates the following query,
UPDATE `my_json_table` SET `containerValue` = '{\"Covid19VaccineStaus\": \"Vaccinated\", \"Date of last vaccine taken\": \"2021-07-13T00:00:00+04:00\", \"If vaccinated, Name of vaccination received\": \"Pfizer-BioNTech\"}' WHERE (`id` = '94');
where it looks like it takes the entire values for the field and then does the update.
What should the query look like if i want to update just the Covid19VaccineStatus key without putting in the values for the other data points for the json schema.
ProDec :
Please take a look at JSON functions\nJSON_REPLACE,\n\nReplace values in JSON document\n\nJSON_REMOVE,\n\nRemove data from JSON document\n\nJSON_INSERT\n\nInsert data into JSON document\n\nUPDATE `my_json_table` SET `containerValue` = JSON_REPLACE(`containerValue`, '$."Covid-19Vaccine Staus"', 'Vaccinated') WHERE (`id` = '94');\n\nUPDATE `my_json_table` SET `containerValue` = JSON_REMOVE(`containerValue`, '$."Covid-19Vaccine Staus"') WHERE (`id` = '94');\n\nUPDATE `my_json_table` SET `containerValue` = JSON_INSERT(`containerValue`, '$."Covid-19Vaccine Staus"', 'Vaccinated') WHERE (`id` = '94');\n\nTo replace a key and keep value\nUPDATE `my_json_table` \nSET `containerValue` = \n JSON_REMOVE(\n JSON_INSERT(`containerValue`, '$."Covid19VaccineStaus"', \n JSON_EXTRACT(`containerValue`, '$."Covid-19Vaccine Staus"')), \n '$."Covid-19Vaccine Staus"') \nWHERE (`id` = '94');\n",
2021-11-07T08:02:51