converting tables to jsonb in postgresql

as json as a datatype for databases is becoming more and more popular here’s a quick example on how to convert a traditional table design to tables containing jsonb in postgres:

-- traditional design
drop schema if exists traditional cascade;
create schema traditional;
create table traditional.customers ( id serial
                                   , name varchar(20)
                                   , active boolean
                                   , country varchar(2)
                                   , phone varchar(20)
                                   , email varchar(50)
                                   );
alter table traditional.customers add constraint customers_pk primary key(id);

create table traditional.orders ( id serial
                                , customer_id int
                                , order_date timestamp with time zone
                                , delivery_date timestamp with time zone
                                );
alter table traditional.orders add constraint orders_pk 
    primary key(id);
alter table traditional.orders add constraint orders_ref_customers 
    foreign key (customer_id) 
    references traditional.customers(id);

DO
$$
BEGIN
   for i in 1..100 loop
     insert into traditional.customers ( name,active,country
                                       , phone,email )
            values ( 'name'||i, case when mod(i,5) = 0 then true else false end
                   , case when mod(i,3) = 0 then 'CH' else 'DE' end
                   , i, i||'@'||i||'.com' );
            for e in 1..10 loop
              insert into traditional.orders ( customer_id, order_date
                                             , delivery_date )
                     values (i, current_timestamp + interval '5 days'
                            , current_timestamp + interval '7 days' );
            end loop;
   end loop;
END
$$;

-- json design 
drop schema if exists jsonschema cascade;
create schema jsonschema;
create table jsonschema.customers ( id serial
                                  , customer_data jsonb
                                  );
alter table jsonschema.customers add constraint customers_pk 
      primary key(id);

create table jsonschema.orders ( id serial
                               , customer_id int
                               , order_data jsonb
                               );
alter table jsonschema.orders add constraint orders_pk primary key(id);
alter table jsonschema.orders add constraint orders_ref_customers 
     foreign key (customer_id) 
     references traditional.customers(id);

insert into jsonschema.customers ( customer_data )
        ( select row_to_json(cust)::jsonb 
            from ( select name, active, country, phone, email
                     from traditional.customers 
                 ) cust 
        );

with tt (id,order_date,delivery_date) as (
  select id,order_date, delivery_date
    from traditional.orders
  order by id )
,    dd (id,customer_id) as (
  select id, customer_id 
    from traditional.orders
  order by id )
insert into jsonschema.orders ( customer_id, order_data )
   select dd.customer_id 
        , row_to_json(tt)::jsonb 
     from dd, tt
    where dd.id = tt.id;

comparing the different approaches:

postgres=# select * from traditional.customers limit 2;
 id | name  | active | country | phone |  email  
----+-------+--------+---------+-------+---------
  1 | name1 | f      | DE      | 1     | 1@1.com
  2 | name2 | f      | DE      | 2     | 2@2.com
(2 rows)

postgres=# select * from jsonschema.customers limit 2;
 id |                                     customer_data                                     
----+---------------------------------------------------------------------------------------
  1 | {"name": "name1", "email": "1@1.com", "phone": "1", "active": false, "country": "DE"}
  2 | {"name": "name2", "email": "2@2.com", "phone": "2", "active": false, "country": "DE"}