mysqlsqljsonlongtext

Update one field in MySQL longtext data type

There is some JSON data stored in MySQL database, with the table schema like this:-

create TABLE ApplicationEntity (
    ->  name varchar(100),
    ->  jsonData longtext
    ->  ) ENGINE=InnoDB Default CHARSET=latin1;

One sample entry for jsonData in the table is like this 

[{"name":"some_name","description":"good_description","gla":"None","srcPort":"123","dstPort":"2345","disableTimeout":false"}]

How can we access the individual key and value pair to the update.

I tried something like this :-

mysql> Update ApplicationEntity set jsonData='%"gla":"google"%' where jsonData like '%"gla":"None"%';
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

But the table came into this form

mysql> select * from ApplicationEntity;
+------+-------------------------------------------------------------------------------------------------------------------+
| name | jsonData                                                                                                     |
+------+-------------------------------------------------------------------------------------------------------------------+
| a1   | %"gla":"google"%                                                                                                    |
| a2   | %"gla":"google"%                                                                                                    |
| a2   | %"gla":"google"%                                                                                                    |
| a2   | %"gla":"google"%                                                                                                    |
| a2   | %"alg":"google"%                                                                                                    |
| a2   | [{"name":"CREATED_IN_CHROME","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}] |
| a2   | [{"name":"CREATED_IN_MOZILL","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}] |
+------+-------------------------------------------------------------------------------------------------------------------+

Obviously this didn't worked, my question is for longtext data type how can we do the updates for individual fields.

So the table should come into this form:

+------+-----------------------------------------------------------------------------------------------------------------------+
| name | jsonData                                                                                                         |
+------+-----------------------------------------------------------------------------------------------------------------------+
| a1   | [{"name":"CREATED_IN_IE","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}]         |
| a2   | [{"name":"CREATED_IN_SAFARI","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}]     |
| a2   | [{"name":"CREATED_IN_OMERTA","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}]     |
| a2   | [{"name":"CREATED_IN_duckduckgo","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}] |
| a2   | [{"name":"CREATED_IN_ucbrowser","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}]  |
| a2   | [{"name":"CREATED_IN_CHROME","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}]     |
| a2   | [{"name":"CREATED_IN_MOZILL","description":"","gla":"google","srcPort":"","dstPort":"2345","disableTimeout":false}]     |
+------+-----------------------------------------------------------------------------------------------------------------------+
Solution

  • Something like this would also work:-

    UPDATE ApplicationEntity
SET jsonData = REPLACE(jsondata, 'None', 'google')
WHERE jsonData LIKE '%"gla":"None"%'

    Where REPLACE is a MySQL string function

    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

    Also we should be aware that regular expressions are not supported by MySQL REPLACE function.