59

In postgresql 9.4 the new JSONB was incorporated.

On a live DB in postgresql 9.3 I have a JSON column.

I want to migrate it to JSONB.

Assuming I migrated the DB first to 9.4 (using pg_upgrade). What do I do next?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Boaz
  • 4,864
  • 12
  • 50
  • 90

2 Answers2

101
ALTER TABLE table_with_json
  ALTER COLUMN my_json
  SET DATA TYPE jsonb
  USING my_json::jsonb;
Seamus Abshere
  • 8,326
  • 4
  • 44
  • 61
Marth
  • 23,920
  • 3
  • 60
  • 72
  • Are there any benchmarks on how fast this is? Ideally with how that scales with the number of records. – gregoltsov Dec 02 '15 at 11:52
  • 1
    Asked this as [a separate question](https://stackoverflow.com/questions/34042429/roughly-how-fast-is-json-jsonb-column-conversion-in-postgres-9-4). – gregoltsov Dec 02 '15 at 12:12
  • 1
    In Rails console: `ActiveRecord::Base.connection.execute('ALTER TABLE table_with_json ALTER COLUMN my_json SET DATA TYPE jsonb USING my_json::jsonb')` – Miguel Peniche Feb 25 '16 at 22:46
  • 1
    This doesn't work when migrating in postgresql 9.5 – Boris Barroso May 26 '16 at 23:08
  • If anyone using old sqlalchemy-utils and is wondering about JSONB not as sqlalchemy-util data type, you can use from sqlalchemy.dialects.postgresql import JSONB in your model. and migrate DB as above answer – Kalpit Champanery Feb 26 '19 at 09:17
38

In the context of Rails, here is an ActiveRecord migration alternative:

def change
  reversible do |dir|
    dir.up { change_column :models, :attribute, 'jsonb USING CAST(attribute AS jsonb)' }
    dir.down { change_column :models, :attribute, 'json USING CAST(attribute AS json)' }
  end
end

I don't know how this compares to the accepted answer performance-wise, but I tested this on a table with 120 000 records, each record having four json columns and it took me about a minute to migrate that table. Of course, I guess it depends on how complex the json structure is.

Also, notice that if your existing records have a default value of {}, you have to add to the above statements default: {}, because otherwise you'll have jsonb columns, but the default value will remain as '{}'::json.

Alexander Popov
  • 23,073
  • 19
  • 91
  • 130
  • Out of curiousity, why would you use `reversible do ... end` rather than `def up ... end` and `def down ... end`? – OzBarry May 15 '17 at 17:55
  • 2
    @OzBarry because of convention and because using `change` in combination with `reversible` is the "recommended" way. Have a look at http://edgeguides.rubyonrails.org/active_record_migrations.html#using-the-change-method, in particular 3.8 Using the change Method, 3.9 Using reversible, 3.10 Using the up/down Methods – Alexander Popov May 16 '17 at 07:34
  • Could you add this answer here? https://stackoverflow.com/questions/50024841/how-do-i-migrate-an-activerecord-model-attribute-from-json-to-jsonb . That way it will be the exact right answer as this question didn't actually mention rails – Lee Dykes Apr 25 '18 at 14:28