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
Peter VARGA :
I personally prefer another method:\n\nUPDATE my_table SET my_col = REPLACE(my_col, '\"oldKeyValue\":', '\"newKeyValue\":')\n\n\nThis replaces directly the key name in the JSON string without destroying the JSON structure.\n\nI am using the additional : in order to avoid an unintentional replacement in a value.",
2019-01-17T17:53:23
Madhur Bhaiya :
There is no straightforward JSON function to do the same. We can use a combination of some JSON functions.\n\nWe will remove the oldKey-oldValue pair using Json_Remove() function, and then Json_Insert() the newKey-oldValue pair.\n\nJson_Extract() function is used to fetch value corresponding to an input key in the JSON document.\n\nUPDATE `my_table` \nSET `my_col` = JSON_INSERT(\n JSON_REMOVE(my_col, '$.oldKeyValue'), \n '$.newKeyValue', \n JSON_EXTRACT(my_col, '$.oldKeyValue')\n );\n\n\nDemo\n\nSET @my_col := '{\"endDate\": \"2018-10-10\", \"startDate\": \"2017-09-05\", \"oldKeyValue\": {\"foo\": 1000, \"bar\": 2000, \"baz\": 3000}, \"anotherValue\": 0}';\n\nSET @new_col := JSON_INSERT(\n JSON_REMOVE(@my_col, '$.oldKeyValue'), \n '$.newKeyValue',\n JSON_EXTRACT(@my_col,'$.oldKeyValue')\n );\n\nSELECT @new_col;\n\n\nResult\n\n| @new_col |\n| ------------------------------------------------------------------------------------------------------------------------------- |\n| {\"endDate\": \"2018-10-10\", \"startDate\": \"2017-09-05\", \"newKeyValue\": {\"bar\": 2000, \"baz\": 3000, \"foo\": 1000}, \"anotherValue\": 0} |\n\n\n\n\nAs an alternative to Json_Extract(), we can also use -> operator to access the Value corresponding to a given Key in the JSON doc.\n\nUPDATE `my_table` \nSET `my_col` = JSON_INSERT(\n JSON_REMOVE(my_col, '$.oldKeyValue'), \n '$.newKeyValue', \n my_col->'$.oldKeyValue' \n );\n",
2018-11-22T14:56:00