CODEWITHSUNDEEP
mysqlsql
Glide
Glide

Reputation: 21265

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.

Upvotes: 1

Views: 3122

Answers (3)

faheem ahmad
faheem ahmad

Reputation: 54

modify Jack's answer. Works perfectly even column value is empty on first update.

update table
 set column_name = case when column_name is null or column_name ='' 

 then "{'foo':'bar'}"
 else CONCAT_WS(",", SUBSTRING(column_name, 1, CHAR_LENGTH(column_name) - 1),SUBSTRING("{'foo':'bar'}", 2))
 end

Upvotes: 0

Panama Jack
Panama Jack

Reputation: 24478

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));

Upvotes: 2

Fabio
Fabio

Reputation: 23510

I think you can use REPLACE function to achieve this

UPDATE table
SET column = REPLACE(column, '{\'name\':\'bob\', \'blob\': {\'foo\':\'bar\'}, \'age\': 12}', '{\'name\':\'bob\', \'blob\': {\'foo\':\'bar\'}, \'age\': 12, \'gender\': \'male\'}')

Take care to properly escape all quotes inside json

Upon you request of nested json, i think you can just remove last character of the string with SUBSTRING function and then append whatever you need with CONCAT

UPDATE table
SET column = CONCAT(SUBSTRING(column, 0, -1), 'newjsontoappend')

Upvotes: 0

Related Questions