Remove a key:value from json string stored in a MySQL database

Question

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.

Answer 1

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