#88 Virtual Columns with JSON Data Types
7-9-2017

Summary

Continuing from Episode #87, we take a deeper dive into virtual columns in Rails 5.1 and use them to parse JSON Data Types with adding indexes to the virtual column.
6
rails database json 7:10

Summary

Terminalrails g scaffold product name properties:json color:virtual size_id:virtual:index
db/migrate/20170707050032_create_products.rbclass CreateProducts < ActiveRecord::Migration[5.1]
  def change
    create_table :products do |t|
      t.string :name
      t.json :properties
      t.virtual :color, type: :string, as: "JSON_UNQUOTE(JSON_EXTRACT(properties, '$.color'))"
      t.virtual :size_id, type: :integer, as: "JSON_UNQUOTE(JSON_EXTRACT(properties, '$.size_id'))", stored: true
      t.timestamps
    end
    add_index :products, :size_id
  end
end
products/_form.html.erb  <%= form.fields_for :properties do |builder| %>
    <%= content_tag :div, class: :field do %>
      <%= builder.label :color, label: 'Color' %>
      <%= builder.text_field :color, value: product.color %>
    <% end %>

    <%= content_tag :div, class: :field do %>
      <%= builder.label :size_id, label: 'Size' %>
      <%= builder.collection_select :size_id, Size.all, :id, :name, selected: product.size_id %>
    <% end %>
  <% end %>
products_controller.rb    def product_params
      params.require(:product).permit(:name, properties: [:color, :size_id])
    end
products/show.html.erb<%= @product.color %>

<%= @product.size&.name %>
product.rbclass Product < ApplicationRecord
  belongs_to :size, optional: true
end
size.rbclass Size < ApplicationRecord
  has_many :products
end


Wolfgang Barth PRO said about 1 month ago:

t.json doesn't work for me with rails 5.1.4, mysql2 0.4.10, and mariadb 10.2.10. I tried

 def change
    add_column :org_units, :ou_logo, :json
  end

after migration, schema.rb results in t.text:

    t.text "ou_logo", limit: 4294967295, collation: "utf8mb4_bin"

I expected t.json. Any ideas?

Wolfgang

kobaltz PRO said about 1 month ago:

With MariaDB, it looks like the JSON data type was introduced in 10.2.7. However, instead of a JSON data type, it creates a LONGTEXT.

https://mariadb.com/kb/en/library/json-data-type/

Wolfgang Barth PRO said about 1 month ago:

Thank you for the link. Mysql and JSON seems to be a trap for me. Most linux distributions migrate mysql to mariadb these days. Migrate data to mariadb needs an explicit dump and restore of the related tables or you change the type back to t.text in mysql.

I will go back use t.text an serialize for now.

Login to Comment