Moving a Column into JSONB Field as Key-CodePudding

I'm using Postgres database and have a table like below

id    firstname    lastname  settings
 1     Sam          Crews    {"some_key": some_value}
 2     John         Dave     {"some_key": some_value}

I am trying to move one of the columns into settings json and drop the column. So it becomes,

id    firstname      settings
 1     Sam           {"some_key": some_value, "lastname": "Crews"}
 2     John          {"some_key": some_value, "lastname": "Dave"}

What is the postgres syntax for moving column to in jsonb field? Been searching but can not seem to figure it out.

CodePudding user response：

You need to run an update to add the key/value pair to the existing JSON value.

update the_table 
   set settings = settings||jsonb_build_object('lastname', lastname)

if it's possible that settings is null, you can e.g. use coalesce()

update the_table 
   set settings = coalesce(settings, '{}) || jsonb_build_object('lastname', lastname)

then you can drop the column

alter table the_table
  drop column lastname;

But this sounds like a bad idea. Why de-normalize this and make your life harder in the long run?