Menu
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
474
JSON_SET() – Insert or Update Values in a JSON Document in MySQL. In MySQL, the JSON_SET() function inserts or updates values in a JSON document and returns the result. You provide the JSON document as the first argument, followed by the path to insert into, followed by the value to insert.
MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.
-- Stored procedure to insert post and tags DROP PROCEDURE IF EXISTS insert_post; DELIMITER $$ CREATE PROCEDURE insert_post( IN my_data JSON ) BEGIN -- Declare iterator variable to use it later on in the loop DECLARE i INT DEFAULT 0; -- Retrieve values from JSON SET @title = JSON_UNQUOTE(JSON_EXTRACT(my_data, '$.
In MySQL, the JSON_EXTRACT() function returns data from a JSON document. The actual data returned is determined by the path you provide as an argument. You provide the JSON document as the first argument, followed by the path of the data to return.
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
