CODEWITHSUNDEEP
ruby-on-railsjsonpostgresqlrails-activerecord
Alex F
Alex F

Reputation: 51

Rails migration - change column from varchar to jsonb

I'm trying to convert an existing column of type varchar to jsonb. The column contains strings like "black white orange" and want to convert it into jsonb format such that it will be converted to ["black", "white", "orange"]. 

class AlterColorsDatatype < ActiveRecord::Migration[5.0]
  def change
    change_column :quotes, :colors, :jsonb, default: '[]', using: 'colors::jsonb'
  end
end

I expected to this to convert the column type to jsonb and the using: part would convert existing data to jsonb as well.

Instead, I get this error:

ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json DETAIL: Token "Indigo" is invalid. CONTEXT: JSON data, line 1: Indigo : ALTER TABLE "quotes" ALTER COLUMN "colors" TYPE jsonb USING colors::jsonb

I have tried other syntax but still ends up with the same error. I am thinking I would have to convert the whole column attribute by attribute with something like to_json but am not sure how to approach solving this error. After many google searches, other people with the same error did not seem to find a solution.

Upvotes: 2

Views: 1801

Answers (1)

mu is too short
mu is too short

Reputation: 434685

You can't get from a space-delimited string to a JSON with a simple cast. An easy way is to first break the string up to get a PostgreSQL array (text[]):

regexp_split_to_array(colors, E'\\s+')

and then convert that array to JSON:

to_json(regexp_split_to_array(colors, E'\\s+'))

You have to be careful with the quoting and backslashes to get that bit of SQL through Ruby and into the database so you'd say:

using: %q{to_json(regexp_split_to_array(colors, E'\\\\s+'))}

The %q{...} is like a single quoted string but lets you avoid having to escape the single quotes in the SQL string literal, then double up your backslashes to keep them from being interpreted by %q{...}.

Upvotes: 2

Related Questions