replace part of a string in database which is unknown..

[t_thakar]

Hi not sure if this is possible so wanted some guidance..

I have a database with 3 columns..

id, data,timestamp

id is auto increment and data is json array and timestamp is unix timestamp..

the field I wanted to update was data but I wanted to replace just a part of it..

see sample data below
(id, data, timestamp)
(1, '{"Employee_Number":"235","First_Name":"Test","Middle_Name":"TEST2"}',1495120538)

I wanted to replace the 235 with 544 but I will not know what data there is in the data field .. I will only know the id

I tried

UPDATE table SET data= replace(data, '{"Employee_Number":"%","First_Name"', '{"Employee_Number":"544","First_Name"') WHERE id = '1'

but the issue is that % cannot be used as wildcard in replace..

Any suggestions will help

Thanks

[cereal]

If you can use the latest version of MySQL, then you can use JSON_REPLACE:

JSON_REPLACE(data, '$.Employee_Number', '544')

See: https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html

[t_thakar]

ahh brill

does the field needs to be specific type? or varchar is ok?

[cereal]

MySQL suggests LONGBLOB or LONGTEXT, however with 5.7.8 there is also the JSON data type:

• https://dev.mysql.com/doc/refman/5.7/en/json.html

I have used the blob type to save small JSON objects, it works fine.