Nsryjdtyk

I'm having this json stored in db

{"endDate": "2018-10-10", "startDate": "2017-09-05", "oldKeyValue": {"foo": 1000, "bar": 2000, "baz": 3000}, "anotherValue": 0}

How can I rename "oldKeyValue" key to "newKeyValue" without knowing the index of the key in an UPDATE query? I'm looking for something like this

UPDATE `my_table` SET `my_col` = JSON()

NOTE: only the key needs to change, the values (i.e. {"foo": 1000, "bar": 2000, "baz": 3000}) should remain the same

There is no straightforward JSON function to do the same. We can use a combination of some JSON functions.

We will remove the oldKey-oldValue pair using Json_Remove() function, and then Json_Insert() the newKey-oldValue pair.

Json_Extract() function is used to fetch value corresponding to an input key in the JSON document.

UPDATE `my_table` 

SET `my_col` = JSON_INSERT(

                           JSON_REMOVE(my_col, '$.oldKeyValue'), 

                           '$.newKeyValue', 

                           JSON_EXTRACT(my_col, '$.oldKeyValue')

                          );

Demo

SET @my_col := '{"endDate": "2018-10-10", "startDate": "2017-09-05", "oldKeyValue": {"foo": 1000, "bar": 2000, "baz": 3000}, "anotherValue": 0}';



SET @new_col := JSON_INSERT(

                            JSON_REMOVE(@my_col, '$.oldKeyValue'), 

                            '$.newKeyValue',

                            JSON_EXTRACT(@my_col,'$.oldKeyValue')

                          );



SELECT @new_col;

Result

| @new_col                                                                                                                        |

| ------------------------------------------------------------------------------------------------------------------------------- |

| {"endDate": "2018-10-10", "startDate": "2017-09-05", "newKeyValue": {"bar": 2000, "baz": 3000, "foo": 1000}, "anotherValue": 0} |

As an alternative to Json_Extract(), we can also use -> operator to access the Value corresponding to a given Key in the JSON doc.

UPDATE `my_table` 

SET `my_col` = JSON_INSERT(

                           JSON_REMOVE(my_col, '$.oldKeyValue'), 

                           '$.newKeyValue', 

                           my_col->'$.oldKeyValue' 

                          );

I personally prefer another method:

UPDATE my_table SET my_col = REPLACE(my_col, '"oldKeyValue":', '"newKeyValue":')

This replaces directly the key name in the JSON string without destroying the JSON structure.

I am using the additional : in order to avoid an unintentional replacement in a value.