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

i have column in table stored in format:

{"field1":"val1","field2":"val4"} {"field1":"val2","field2":"val5"} {"field1":"val3","field2":"val6"}

i need remove field1 values(e.g "field1":"val1","field1":"val2","field1":"val3" ) , result should be

{"field2":"val4"} {"field2":"val5"} {"field2":"val6"}

i trying acheive via replace stuck in '"field1":"val1"' string val1 value null, integer.

update emp set col = replace(col, '"field1":"val1"', '')

i stuck due dynamic value of val1.

you can this:

select substring(field, 1, instr(field, '"field1"')) + substring(field, instr(field, '"field2"'), length(field)) @temp

i don't know if works idea. (can't test atm)

here mssql equivalent (works, tested!):

select substring(field, 0, charindex('"field1"', field)) + substring(field, charindex('"field2"', field), len(field)) @temp

Look