Glide Glide - 7 months ago 17
SQL Question

MySQL query to append key:value to JSON string

My table has a column with a JSON string that has nested objects (so a simple REPLACE function cannot solve this problem) . For example like this:

{'name':'bob', 'blob': {'foo':'bar'}, 'age': 12}
. What is the easiest query to append a value to the end of the JSON string? So for the example, I want the end result to look like this:
{'name':'bob', 'blob': {'foo':'bar'}, 'age': 12, 'gender': 'male'}
The solution should be generic enough to work for any JSON values.

Answer

What about this

UPDATE table SET table_field1 = CONCAT(table_field1,' This will be added.');

EDIT:

I personally would have done the manipulation with a language like PHP before inserting it. Much easier. Anyway, Ok is this what you want? This should work providing your json format that is being added is in the format {'key':'value'}

 UPDATE table
 SET col = CONCAT_WS(",", SUBSTRING(col, 1, CHAR_LENGTH(col) - 1),SUBSTRING('newjson', 2));