Change Key Name in nested JSON mysql-CodePudding

I want to change the Key Name of the first and second level of the JSON in mysql. For this I have a table called content and a column called attribs and the data stored is as follows:

{"author": "Nika", "link_url": "", "gallery": "{\"gallery_images\":[\"images/2017/09/30/blog_011.jpg\",\"images/2017/09/30/blog_010.jpg\",\"images/2017/09/30/blog_009.jpg\"]}", "show_urls": ""}

I would like it to be as follows:

{"post_author": "Nika", "link_url": "", "gallery": "{\"post_gallery_images\":[\"images/2017/09/30/blog_011.jpg\",\"images/2017/09/30/blog_010.jpg\",\"images/2017/09/30/blog_009.jpg\"]}", "show_urls": ""}

In sumary I want change author by post_author and gallery_images by post_gallery_images

UPDATE: I tried following; but it only change first level. I tried other methos from Mysql Docs and Answers here in Stackoverflow but not wor for second level.

UPDATE adneit_content SET attribs = REPLACE(attribs, '"author":', '"post_author":');

Thanks.

CodePudding user response：

To update the nested keys, you need to match the backslashes as well.

UPDATE adneit_content 
SET attribs = REPLACE(
                REPLACE(attribs, '"author":', '"post_author":'), 
                '\\"gallery_images\\":', '\\"post_gallery_images\\":')