DEV Community

Cover image for Convert YAML column data to JSONB in Rails and Postgres
Edwin Mak
Edwin Mak

Posted on • Originally published at edwinthinks.com

Convert YAML column data to JSONB in Rails and Postgres

Introduction

My team had wanted to take advantage of the incredibly useful JSON features provided by Postgres 9.4+ in working with data that our rails application was storing as YAML in a text column. We had installed a very useful gem called audited that was responsible for keeping track of "when" & "what" changed in our ActiveRecord records. The "what" data was, unfortunately, being stored as YAML in a text column and makes querying this data more difficult than it needs to be. Here is a sample of that "what" data:

---
pilot:
- false
- true
Enter fullscreen mode Exit fullscreen mode

And this is what I want it to be like:

{
  "pilot": [false, true]
}
Enter fullscreen mode Exit fullscreen mode

Notably, the authors of the audited gem included the option to install it such that this data is stored in JSONB (default is YAML).

Lately, we have been interested in building more advanced queries to search by the content of this "what" data. As a result, we aimed to create a migration that can transform the data safely back and forth from YAML to JSONB (reversible for good measure).

The Result

Here is a migration file that we ended up generating that converts the audited_changes column (that "what" data) on the audits table from YAML to JSONB. You can also utilize this snippet of code to transform your YAML column to JSONB.

Disclaimer - Migrate at your own risk!

class ChangeAuditedChangesFromYamlToJson < ActiveRecord::Migration
  def up
    updated_records = fetch_audit_records.map do |r|
      {
        id: r['id'],
        audited_changes: escape_sql(JSON.dump(YAML.safe_load(r['audited_changes'])))
      }
    end

    remove_column :audits, :audited_changes
    add_column :audits, :audited_changes, :jsonb

    Transaction.transaction do
      updated_records.each do |ur|
        ActiveRecord::Base.connection.execute(
          """
            UPDATE audits 
            SET audited_changes = '#{ur[:audited_changes]}'::jsonb 
            WHERE id = #{ur[:id]}
          """
        )
      end
    end
  end

  def down
    updated_records = fetch_audit_records.map do |r|
      {
        id: r['id'],
        audited_changes: escape_sql(YAML.dump(JSON.parse(r['audited_changes'])))
      }
    end

    remove_column :audits, :audited_changes
    add_column :audits, :audited_changes, :text

    Transaction.transaction do
      updated_records.each do |ur|
        ActiveRecord::Base.connection.execute(
          """
            UPDATE audits 
            SET audited_changes = '#{ur[:audited_changes]}'::text 
            WHERE id = #{ur[:id]}
          """
        )
      end
    end
  end

  private

  #
  # Returns the id & audit_changes column of the audits table.
  # @return [PG::Result]
  def fetch_audit_records
    ActiveRecord::Base.connection.execute('SELECT id, audited_changes FROM audits')
  end

  #
  # Returns the SQL escaped version of the string provided 
  # For example, a "'" needs to be escaped by doubling it.
  # @param [String] the string to be converted
  # @return [String] the escaped version of the string provided
  def escape_sql(string)
    return string.gsub("'","''")
  end

end
Enter fullscreen mode Exit fullscreen mode

Conclusion

It took some trial and error and navigating through some 'gotchas' to arrive at our resultant migration. For instance, we had to escape single-quote characters by adding another single-quote next to every instance (See the escape_sql method in the code snippet). Nonetheless, we are happy with the results and excited to start building queries with those super awesome JSON features given to us by Postgres.

Hope this helps someone else =). Happy coding!

Oldest comments (1)

Collapse
 
randallconway profile image
Randall Conway

Hello Edwin! I have the same issue but the problem is I only have access to the database and not with the rails app. I need to generate a report. Is there a way from SQL(PG) perspective to convert that data column to JSON?

e.g SELECT jsonb_build_object(data) FROM versions;

thanks!