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.

You would need to use regular expressions to achieve this. See this post. However, some people seem to have found a workaround. — mikeyq6
– mikeyq6, Commented Mar 1, 2016 at 14:12
If you are using php, you can iterate each row and use the code below to get that particular row without field1 $str='{"field1":"val1","field2":"val4"}'; $array = json_decode($str); foreach ($array as $key => $value) { if($key=="field1") continue; else echo "$key:$value<br>"; } — Rajesh
– Rajesh, Commented Mar 1, 2016 at 14:31

Eric Lavoie · Accepted Answer · 2017-07-10 21:18:14Z

65

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

answered Jul 10, 2017 at 21:18

Eric Lavoie

5,4813 gold badges35 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ferhat Sayan · Accepted Answer · 2016-03-02 08:22:26Z

-3

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

edited Mar 2, 2016 at 8:22

answered Mar 1, 2016 at 14:21

Ferhat Sayan

2361 gold badge4 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related