CODEWITHSUNDEEP
mysqlsqlmysql-json
ABC
ABC

Reputation: 4373

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

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.

Upvotes: 22

Views: 24816

Answers (2)

Eric Lavoie
Eric Lavoie

Reputation: 5431

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

Upvotes: 63

Ferhat Sayan
Ferhat Sayan

Reputation: 226

You can do it like this:

SELECT SUBSTRING(Field, 1, INSTR(Field, '"field1"')) + SUBSTRING(Field, INSTR(Field, '"field2"'), LENGTH(Field)) FROM @Temp

I don't know if this works but this is the idea. (Can't test ATM)

Here is the MsSQL equivalent (works, just tested!):

SELECT SUBSTRING(Field, 0, CHARINDEX('"field1"', Field)) + SUBSTRING(Field, CHARINDEX('"field2"', Field), LEN(Field)) FROM @Temp

Upvotes: -3

Related Questions