Update Values in JSON Data Type Column by given key-CodePudding

My website uses mysql. Unfortunately, cuz of previous developer, there is a terrible database design and making a change in it takes a lot of time.

I want to explain what i want to do. I have table that is named content.

id	content	category_id
1	{ "content_de": "some german content", "content_en": ""}	150
2	{ "content_de": "some german content 2", "content_en": ""}	150

So, as you can see, content_en key is empty inside the json in the content column. I want to copy the values of content_de key into value of content_en key where category_id is 150.

The result should be like after query.

id	content	category_id
1	{ "content_de": "some german content", "content_en": "some german content"}	150
2	{ "content_de": "some german content 2", "content_en": "some german content 2"}	150

How can I accomplish this task?

CodePudding user response：

You can perform partial update of the JSON values through use of JSON_SET() function such as

UPDATE person p
   SET content = JSON_SET(
                          content, 
                          '$.content_en', 
                          JSON_EXTRACT(content, '$.content_de')
                         )
 WHERE category_id = 150

Demo

The above one works for the version 5.7 of the database too, but if the current version of your database is 8.0 , then the following might be another option to use

UPDATE person p
  JOIN JSON_TABLE(
                 p.content,
                '$.content_de' COLUMNS (extracted_content VARCHAR(800) PATH '$')
       ) j
   SET content = JSON_SET(content, '$.content_en', extracted_content)
 WHERE category_id = 150

CodePudding user response：

I have found my solution. I had to use stored procedure to iteratively to update the value of keys in the rows.

Also there were so many keys (not only content_de) in the json data of content row. So i just created and declared queries for each key one by one in the while loop.

Actually, in the first, I tried to use wildcards for not applying this way.

That could be like; "Find the keys which end with _de, then replace the value of them with values of keys which end with _en". But then I have realized that would require another control mechanism like match the same keys etc.

I just solved my problem with this query.

DROP PROCEDURE IF EXISTS updateJSONData;
DELIMITER //
CREATE PROCEDURE updateJSONData()
BEGIN

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

SET @menuID = 151;
SET i = 0;

select COUNT(id) from sayfa_icerik where menuID = @menuID and durum = 1 and lang_en = 1 INTO n;

WHILE i < n DO 

    SET @id = (select id from sayfa_icerik where menuID = @menuID and durum = 1 and lang_en = 1 LIMIT i,1);

    set @sertifika = (SELECT json_extract(icerik, '$.sertifika_de') FROM sayfa_icerik WHERE menuId = @menuID and id = @id);

    update sayfa_icerik set icerik = json_replace(icerik, '$.sertifika_en', JSON_UNQUOTE(@sertifika)) where menuId = @menuID and id = @id;

    ...


    set @video = (SELECT json_extract(icerik, '$.video_de') FROM sayfa_icerik WHERE menuId = @menuID and id = @id);

    update sayfa_icerik set icerik = json_replace(icerik, '$.video_en', JSON_UNQUOTE(@video)) where menuId = @menuID and id = @id;

 
   ...

  SET i = i   1;

END WHILE;

END //
call updateJSONData;

_{Note: answer provided by OP on question section.}