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.

user330315 · Accepted Answer · 2022-04-13 18:25:15Z

3

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?

answered Apr 13, 2022 at 18:25

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Hamza Yerlikaya Over a year ago

actual column is part of setting rest of the settings is kept as jsonb field that is the column i am trying to move.

Collectives™ on Stack Overflow

Moving a Column into JSONB Field as Key

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related