Migrate JSONB data to columns-CodePudding

I have a PostgreSQL database with the following schema:

CREATE TABLE myrecords (data JSONB);

It has some records that look like this:

                    data                     
---------------------------------------------
 {"field1": "enabled", "field2": "disabled"}

I'd like to migrate the data from a JSON blob to SQL columns; I altered the table like so:

ALTER TABLE myrecords ADD COLUMN field1 BOOLEAN;
ALTER TABLE myrecords ADD COLUMN field2 BOOLEAN;

I've managed to convert the JSON object to a row using jsonb_to_record, but I can't figure out how to (a) convert the "enabled"/"disabled" string literals to BOOLEAN TRUE/FALSE values, and (b) update the row to set the new column values. Is it possible to update a row while at the same time selecting data from it?

CodePudding user response：

You can use ->> then compare the result to get a boolean value:

update myrecords
  set field1 = (data ->> 'field1') = 'enabled',
      field2 = (data ->> 'field2') = 'enabled'
;