Virtual Columns with JSON Data Types

Episode #88 by David Kimura

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.
rails database json 7:10

Resources

Summary

# Terminal
rails g scaffold product name properties:json color:virtual size_id:virtual:index

# db/migrate/20170707050032_create_products.rb
class 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.rb
class Product < ApplicationRecord
  belongs_to :size, optional: true
end

# size.rb
class Size < ApplicationRecord
  has_many :products
end