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.
Add a comment
|
3 Answers
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));
2 Comments
Glide
I think this would just concat the value to the end of the string. I need to add it within the json. For example, your solution would turn
{'abc':'123'} to {'abc':123'}This will be added. I need the a json string like {'abc':'123', 'key':'value'}
Panama Jack
Ok I updated my answer. It seemed to work for me with the example you provided.
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')
3 Comments
Glide
That's a nice solution but I need a query that works with any json value. I don't know the json value in the column beforehand.
Fabio
@Glide If you just need to append something at the end you could replace final bracket
} with yournewjsondata }Glide
the thing is, there are nested json objects inside the json string. so replace
} WITH myjsondata would turn this {'name':'bob', 'blob': {'foo':'bar'}, 'age': 12} to {'name':'bob', 'blob': {'foo':'bar','jsonkey':'jsonvalue'}, 'age': 12, 'jsonkey':'jsonvalue'}. I only want to append it to the very end of the json string.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
