18

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.

Eric Lavoie
  • 5,121
  • 3
  • 32
  • 49
ABC
  • 4,263
  • 10
  • 45
  • 72
  • You would need to use regular expressions to achieve this. See [this post](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql). However, some people seem to have found a workaround. – mikeyq6 Mar 01 '16 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
    "; }     – Rajesh Mar 01 '16 at 14:31

2 Answers2

58

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

Eric Lavoie
  • 5,121
  • 3
  • 32
  • 49
-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
Ferhat Sayan
  • 216
  • 1
  • 4
  • 19