Update the key value of a json field in mysql-CodePudding

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.

CodePudding user response：

Please take a look at JSON functions

JSON_REPLACE,

Replace values in JSON document

JSON_REMOVE,

Remove data from JSON document

JSON_INSERT

Insert data into JSON document

UPDATE `my_json_table` SET `containerValue` = JSON_REPLACE(`containerValue`, '$."Covid-19Vaccine Staus"', 'Vaccinated') WHERE (`id` = '94');

UPDATE `my_json_table` SET `containerValue` = JSON_REMOVE(`containerValue`, '$."Covid-19Vaccine Staus"') WHERE (`id` = '94');

UPDATE `my_json_table` SET `containerValue` = JSON_INSERT(`containerValue`, '$."Covid-19Vaccine Staus"', 'Vaccinated') WHERE (`id` = '94');

To replace a key and keep value

UPDATE `my_json_table` 
SET `containerValue` = 
    JSON_REMOVE(
        JSON_INSERT(`containerValue`, '$."Covid19VaccineStaus"', 
            JSON_EXTRACT(`containerValue`, '$."Covid-19Vaccine Staus"')), 
        '$."Covid-19Vaccine Staus"') 
WHERE (`id` = '94');

CodePudding user response：

You can use JSON_SET which handles both insert and update actions.

UPDATE my_json_table
SET containerValue = JSON_SET(containerValue, '$."Covid-19Vaccine Staus"', 'Vaccinated')
WHERE id = 94;

So if your key does not exist yet in your JSON, it will be inserted with the value Vaccinated. Otherwise, the value corresponding to your key will be updated.

You can also find examples here on how to handle arrays or multiple values with JSON_SET.

If you only need to update the value but not perform any insertion in your JSON if the key does not exist, you can use JSON_REPLACE.

If you only need to insert the key and the value but not perform any update in your JSON if the key already exists, you can use JSON_INSERT.

If you want to update the name of your key:

UPDATE my_json_table
SET containerValue = JSON_INSERT(
   JSON_REMOVE(containerValue, '$."Covid-19Vaccine Staus"'),
   '$.Covid19VaccineStaus',
   JSON_EXTRACT(containerValue, '$."Covid-19Vaccine Staus"')
)
WHERE id = 94;