Update and replace unquoted JSON strings-CodePudding

I have the following table in my database:

              Type | Name
-------------------------------------------------
  INT(10) UNSIGNED | id
          LONGTEXT | settings

The settings column holds JSON strings such as the following:

'[
    {"value":"1","label":"user_type"},
    {"value":"2","label":"email_type"}
]'

I have some corrupt data that doesn't correspond to the required format as the requirements have now changed.

'[
    {"value": 8,"label":"should_receive_notifications"},
]'

Notice how the value is unquoted compared to the first example which is how I need them.

Is there a way I can do a find and replace on all JSON strings within the settings column to update all unquoted values in the JSON string and wrap them in quotes?

CodePudding user response：

You may use the next procedure:

CREATE PROCEDURE quote_value(max_amount INT)
BEGIN
REPEAT
    UPDATE test
    SET settings = JSON_REPLACE(settings, CONCAT('$[', max_amount, '].value'), CAST(JSON_UNQUOTE(JSON_EXTRACT(settings, CONCAT('$[', max_amount, '].value'))) AS CHAR));
    SET max_amount = max_amount - 1;
UNTIL max_amount < 0 END REPEAT;
END

max_amount parameter defines the amount of objects in the array to be updated (do not forget that the array elements are counted from zero). So set it to max objects per array amount value.

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=166f43d44e57b62da034bd9530713beb

CodePudding user response：

This is under assumption that there are no spaces between the characters in json string, simple but data needs to be verified for this.

update tablename
set settings = replace(settings, '"value\":' , '"value":\"')
where settings not like '%"value":"%'

update tablename 
set settings = replace(settings, ',"' , '","')
where settings not like '%","%'