Update and replace unquoted JSON strings

Question

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?

My initial idea is to use something such as JSON_extract and do something like the following: SELECT json_extract(settings, '$[*].value') FROM table; — user18801656
– user18801656, Commented May 18, 2022 at 10:09
Does the structure of JSON matches shown example value completely? Does "value" is the only property needed in such quoting? — Akina
– Akina, Commented May 18, 2022 at 10:09
Yes, it's just the 'value' property that needs its assigned value in quotes. These are always IDs, so i'd like to wrap them all in quotes. — user18801656
– user18801656, Commented May 18, 2022 at 10:11

Akina · Accepted Answer · 2022-05-18 11:16:45Z

1

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

edited May 18, 2022 at 11:16

answered May 18, 2022 at 10:33

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user18801656 Over a year ago

Is there a way i can allow the procedure to accept other parameters such as table name and column name if i want to make it more flexible. Or would i need to wrap it in a variable and turn it into a prepared statement?

Akina Over a year ago

@Dmc If you need to parametrize objects names than dynamic SQL is the only option.

Rajani B · Accepted Answer · 2022-05-18 10:47:54Z

0

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 '%","%'

answered May 18, 2022 at 10:47

Rajani B

2031 silver badge5 bronze badges

Collectives™ on Stack Overflow

Update and replace unquoted JSON strings

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related