How do I migrate an ActiveRecord model attribute from json to jsonb?

Question

What should the migration look like? I would like to take advantage of the jsonb array querying technique.

Answer 1

I would write the migration this way:

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 performance-wise to other solutions, 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.