Search code examples
sqljsonpostgresql

How to add new key inside json blob in a column in SQL

I have a SQL table MYTABLE similar to the below example

id.     config
123.    {"location":zxc, 'zip_code':1234} 
143.    {"location":zxc, 'zip_code':1222}

I need to do a database migration where I add a key name inside the config JSON so the config becomes. {"name": "abc", "location":zxc, 'zip_code':1234}

I am not sure how to do this. I can do ALTER TABLE, ADD COLUMN but this is different than adding/removing a column.

Please suggest.

EDIT:

This is in Postgres

Solution

  • This can be done using an UPDATE statement:

    update mytable
  set config = config || '{"name": "abc"}' ;

    The above assumes that config is defined as jsonb (which it should be). If it's a json column, you need to cast it: set config = config::jsonb || '{"name": "abc"}'