I have a JSON structured string stored in table "table", field "field", and need to modify this string directly for a database migration.
Let's say the string looks like {"foo": false," bar": true}, but can have an arbitrary length based off the number of key-value pairs in the string.
What I need to do is append this string on the fly, adding extra key-value pairs as needed.
My thoughts on how to do this would be perform something along the lines of:
UPDATE table SET field = CONCAT(SUBSTR(field, 0, MAX(LENGTH(field))-1), ',"baz":false}. The idea of this is to get a substring based on the max length - 1 of the string, and concat this with the new key-value pair.
SQL is not my strongest suit, so any help with this would be great, thanks!
