I have a column in table which is stored in format:
{"field1":"val1","field2":"val4"}
{"field1":"val2","field2":"val5"}
{"field1":"val3","field2":"val6"}
I need to remove all field1 with values(e.g "field1":"val1","field1":"val2","field1":"val3" ) and result should be
{"field2":"val4"}
{"field2":"val5"}
{"field2":"val6"}
I am trying to acheive this via replace but stuck as in '"field1":"val1"' string val1 could be any value like null, some integer.
UPDATE emp SET col = REPLACE(col, '"field1":"val1"', '')
I am stuck due to this dynamic value of val1.
I would prefer to use the JSON_REMOVE function (MySQL) :
UPDATE emp
SET emp.col = JSON_REMOVE(emp.col, '$.field1');
You can also add a WHERE clause :
WHERE emp.col LIKE '%val6%';
References: MySQL JSON_REMOVE and MySQL JSON path
A blog post with examples: MySQL for your JSON
And a note about json path in MySQL:
Propery names in path must be double quoted if the property identifier contains interpunction (spaces, special characters, meta characters) bugs.mysql.com