MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT, settings LONGTEXT);
INSERT INTO test VALUES
(1, '[\n    {"value":"1","label":"user_type"},\n    {"value":"2","label":"email_type"}\n]'),
(2, '[\n    {"value":1,"label":"user_type"},\n    {"value":"2","label":"email_type"}\n]'),
(3, '[\n    {"value":1,"label":"user_type"},\n    {"value":2,"label":"email_type"},\n    {"value":3,"label":"email_type"}\n]');
SELECT * FROM test;

Records: 3  Duplicates: 0  Warnings: 0




id
settings


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

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

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


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
CALL quote_value(1); -- max_amount=1 means that objects #0 and #1 must be updated
                     -- and the object #2 in the row id=3 must stay unchanged
SELECT * FROM test;


id
settings


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

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

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

id	settings
1	[ {"value":"1","label":"user_type"}, {"value":"2","label":"email_type"} ]
2	[ {"value":1,"label":"user_type"}, {"value":"2","label":"email_type"} ]
3	[ {"value":1,"label":"user_type"}, {"value":2,"label":"email_type"}, {"value":3,"label":"email_type"} ]

id	settings
1	[{"label": "user_type", "value": "1"}, {"label": "email_type", "value": "2"}]
2	[{"label": "user_type", "value": "1"}, {"label": "email_type", "value": "2"}]
3	[{"label": "user_type", "value": "1"}, {"label": "email_type", "value": "2"}, {"label": "email_type", "value": 3}]