Question: 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

Answer: 
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()`](https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-remove) function, and then [`Json_Insert()`](https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-insert) the *newKey-oldValue* pair.

[`Json_Extract()`](https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_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](https://www.db-fiddle.com/f/kHywwDrV1acnxb3x6HkWrT/0)**

```
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' 
                          );

```
Plain text search & replace will only work if the JSON is stored in minified/compact format without extra whitespace in it.