Rails migration to change column type from text to json (Postgresql)

Question

I've been trying unsuccessfully to change a column type in my Postgres database from text to json. Here's what I've tried...

class ChangeNotesTypeInPlaces < ActiveRecord::Migration[5.0]
  def up
    execute 'ALTER TABLE places ALTER COLUMN notes TYPE json USING (notes::json)'
  end

  def down
    execute 'ALTER TABLE places ALTER COLUMN notes TYPE text USING (notes::text)'
  end
end

Also...

class ChangeNotesTypeInPlaces < ActiveRecord::Migration[5.0]
  def up
    change_column :places, :notes, 'json USING CAST(notes AS json)'
  end

  def down
    change_column :places, :notes, 'text USING CAST(notes AS text)'
  end
end

Both of these return the same error...

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json

Answer 1

Using Rails 5.1.x and PostgreSQL 9.4, here is what worked for me when converting text columns (containing valid json) to jsonb columns :

class ChangeTextColumnsToJson < ActiveRecord::Migration[5.1]
  def change
    change_column :table_name, :column_name, :jsonb, using: 'column_name::text::jsonb'
  end
end

Answer 2

I was able to accomplish it using:

def change
  change_column :places, :notes, :json, using: 'notes::JSON'
end

This won't be reversible though; I imagine you can split it out into separate up and down definitions, but I didn't feel the need to.