Moving a Column into JSONB Field as Key

Question

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.

Answer 1

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?