SQL statement to convert jsonb hash to json string

Question

I have a rails data migration (postgres db) where I have to use pure sql to convert the data due to some model restrictions. The data is stored as json as a string, but I need it to be a usable hash for other purposes.

My migration works to convert it to the hash. However, my down method ends up just deleting the data or leaving it as an empty {}. Btw to clear up any confusion, my column name is actually saved as data in table Games

Based on my up method, how would i properly reverse the migration using sql only?

class ConvertGamesDataToJson < ActiveRecord::Migration[6.0]
  def up
    statement = <<~SQL
      update games set data = regexp_replace(trim(both '"' from data::text), '\\\\"', '"', 'g')::jsonb;
    SQL

    ActiveRecord::Base.connection.execute(statement)
    # this part works!
  end

  def down
    statement = <<~SQL
      update games set data = to_json(data::text)::jsonb;
    SQL

    ActiveRecord::Base.connection.execute(statement)
  end
end

Here is how the it looks after properly converting it

data: {
  "id"=>"d092a-f2323",
  "recent"=>'yes',
  "note"=>"some text",
  "order"=>1
}

how it is before the migration and what it needs to rollback to:

data: 
  "{
    \"id\":\"d092a-f2323\",
    \"recent\":\"yes\",
    \"note\":\"some text\",
    \"order\":1,
  }"

Can you show the schema for games, please? And please show a sample of the data in question. If it is truly JSON you should be able to just change the column type: alter table games alter column data type jsonb USING data::jsonb; Can you explain what "Btw to clear up any confusion, my column name is actually saved as data in table Games" means? I wasn't confused before, now I am. :) — Schwern
– Schwern, Commented Jun 17, 2020 at 17:28
Are you sure those \" escapes are really there and not just an artifact of how it's displayed? Try using puts. — Schwern
– Schwern, Commented Jun 17, 2020 at 17:32

Schwern · Accepted Answer · 2020-06-17 17:36:28Z

1

If you're displaying a data structure in the rails console, those \" aren't really there. They're just formatting because the console has wrapped the string in ". For example...

[2] pry(main)> %{"up": "down"}
=> "\"up\": \"down\""

But if we print it...

[3] pry(main)> puts %{"up": "down"}
"up": "down"

Given that is a JSON string, you can simply change the type of the column to jsonb and be done with it.

-- up
alter table games alter column data type jsonb USING data::jsonb;

-- down
alter table games alter column data type text;

Postgres doesn't know how to automatically cast text to jsonb, so we need to tell it. using data::jsonb does a simple cast of the text to jsonb. It can cast jsonb to text just fine.

You can do this in a migration with change_column.

def up
  change_column :users, :data, :jsonb, using: 'data::jsonb'
end

def down
  change_column :users, :data, :text
end

answered Jun 17, 2020 at 17:36

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Over a year ago

I'm doing exactly that an somehow the migration seems to be converting the data to a Ruby hash.

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json; DETAIL:  Token "=" is invalid.; CONTEXT:  JSON data, line 1: {"first_name"=...

Schwern Over a year ago

@Daniel "The data is stored as json as a string" I expect you actually stored it as a Ruby Hash. You will have to convert the existing data to JSON, then migrate the column to jsonb.

Daniel Over a year ago

Yep, a few entities had a Ruby hash stored from an erroneous import 🙈 Converting them to JSON fixed it.

Collectives™ on Stack Overflow

SQL statement to convert jsonb hash to json string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related