Rails 8 JSON Columns with MySQL

January 29, 2026

Relational databases excel at structured data, but sometimes applications need flexibility. User preferences, API responses, metadataβ€”these don't always fit neatly into columns. MySQL's native JSON column type brings document-style storage to Rails without abandoning Active Record.

When JSON Columns Make Sense

JSON columns work best for semi-structured data that varies between records. Product attributes differ between categories (books have ISBNs, clothing has sizes), user settings change over time, and integration data arrives in unpredictable shapes. Rather than creating sparse tables with dozens of nullable columns or building complex EAV (Entity-Attribute-Value) structures, JSON columns provide a clean middle ground.

The tradeoff is clear: JSON columns sacrifice some query performance and referential integrity for flexibility. Use them for data that doesn't need frequent filtering or joining, not as a replacement for proper schema design.

Setting Up JSON Columns

Creating a JSON column in MySQL through Rails migrations requires minimal configuration. MySQL validates JSON syntax automatically, rejecting malformed data at the database level.

# db/migrate/20260129000001_add_settings_to_users.rb
class AddSettingsToUsers < ActiveRecord::Migration[8.0]
  def change
    add_column :users, :settings, :json, null: false, default: {}
    add_column :users, :metadata, :json
  end
end

The model gains immediate access to the JSON data as a Ruby hash. Rails handles serialization transparently, converting between Ruby objects and JSON strings.

# app/models/user.rb
class User < ApplicationRecord
  # JSON columns work automatically in Rails 8
  # No serialize declaration needed for :json type columns
  
  validates :settings, presence: true
  
  def dark_mode?
    settings.dig('appearance', 'dark_mode') == true
  end
  
  def notification_preference(channel)
    settings.dig('notifications', channel) || 'default'
  end
end

Accessing nested values uses standard Ruby hash methods. The dig method safely navigates nested structures without raising errors on missing keys.

Validating JSON Structure

Database-level JSON validation only checks syntax, not structure. Application-level validation ensures the data matches expected shapes. A custom validator handles this cleanly.

# app/validators/json_schema_validator.rb
class JsonSchemaValidator < ActiveModel::EachValidator
  def validate_each(record, attribute, value)
    return if value.blank? && options[:allow_blank]
    
    schema = options[:schema]
    errors = validate_against_schema(value, schema, [])
    
    errors.each do |error|
      record.errors.add(attribute, error)
    end
  end
  
  private
  
  def validate_against_schema(value, schema, path)
    errors = []
    
    schema.each do |key, rules|
      current_path = path + [key]
      field_value = value.is_a?(Hash) ? value[key.to_s] : nil
      
      if rules[:required] && field_value.nil?
        errors << "missing required field: #{current_path.join('.')}"
      end
      
      if rules[:type] && field_value.present?
        unless field_value.is_a?(rules[:type])
          errors << "#{current_path.join('.')} must be a #{rules[:type]}"
        end
      end
      
      if rules[:nested] && field_value.is_a?(Hash)
        errors += validate_against_schema(field_value, rules[:nested], current_path)
      end
    end
    
    errors
  end
end

Apply the validator to models with specific schema requirements:

# app/models/product.rb
class Product < ApplicationRecord
  ATTRIBUTES_SCHEMA = {
    dimensions: {
      type: Hash,
      nested: {
        width: { type: Numeric },
        height: { type: Numeric },
        unit: { type: String, required: true }
      }
    },
    tags: { type: Array }
  }.freeze
  
  validates :attributes, json_schema: { schema: ATTRIBUTES_SCHEMA, allow_blank: true }
end

Querying JSON Data in MySQL

MySQL provides operators for querying inside JSON columns. Rails 8 supports these through the query interface, though some operations require raw SQL fragments.

# app/models/user.rb
class User < ApplicationRecord
  # Find users with dark mode enabled
  scope :dark_mode_enabled, -> {
    where("JSON_EXTRACT(settings, '$.appearance.dark_mode') = true")
  }
  
  # Find users with specific notification setting
  scope :with_notification_setting, ->(channel, value) {
    where("JSON_EXTRACT(settings, ?) = ?", "$.notifications.#{channel}", value)
  }
  
  # Find users where settings contain a specific key
  scope :has_setting, ->(key_path) {
    where("JSON_EXTRACT(settings, ?) IS NOT NULL", "$.#{key_path}")
  }
end

For better query performance on frequently accessed JSON paths, MySQL supports generated columns with indexes:

# db/migrate/20260129000002_add_generated_column_for_json_index.rb
class AddGeneratedColumnForJsonIndex < ActiveRecord::Migration[8.0]
  def up
    execute <<-SQL
      ALTER TABLE users
      ADD COLUMN dark_mode_setting BOOLEAN
      GENERATED ALWAYS AS (JSON_EXTRACT(settings, '$.appearance.dark_mode')) STORED
    SQL
    
    add_index :users, :dark_mode_setting
  end
  
  def down
    remove_index :users, :dark_mode_setting
    remove_column :users, :dark_mode_setting
  end
end

Generated columns extract JSON values into indexable fields, giving query performance close to regular columns while maintaining the source data in JSON format.

Updating JSON Data Safely

Partial updates to JSON columns require care. Naive approaches can cause race conditions or data loss. MySQL's JSON functions enable atomic updates without loading the full document.

# app/models/user.rb
class User < ApplicationRecord
  def update_setting(path, value)
    # Atomic update using MySQL JSON_SET
    self.class.where(id: id).update_all(
      ["settings = JSON_SET(COALESCE(settings, '{}'), ?, ?)", "$.#{path}", value.to_json]
    )
    reload
  end
  
  def remove_setting(path)
    self.class.where(id: id).update_all(
      ["settings = JSON_REMOVE(settings, ?)", "$.#{path}"]
    )
    reload
  end
  
  def merge_settings(new_settings)
    # Deep merge at database level
    self.class.where(id: id).update_all(
      ["settings = JSON_MERGE_PATCH(COALESCE(settings, '{}'), ?)", new_settings.to_json]
    )
    reload
  end
end

These methods bypass Active Record's dirty tracking intentionally. For most updates, standard attribute assignment works fine. Reserve atomic updates for high-concurrency scenarios or background jobs where race conditions matter.

Common Mistakes to Avoid

Several patterns cause problems with JSON columns. Storing data that needs frequent joins or foreign key relationships belongs in regular columns or separate tables. JSON columns can't enforce referential integrity, so related IDs stored in JSON become orphan risks.

Deep nesting creates another problem. While MySQL handles nested JSON well, deeply nested structures become hard to query and validate. Flatten structures where possible, keeping nesting to two or three levels maximum.

Avoid storing large arrays that grow unbounded. User activity logs, for example, should live in separate tables with proper indexes. JSON columns work poorly when documents grow to hundreds of kilobytes.

Summary

JSON columns in MySQL provide flexibility for semi-structured data without abandoning the relational model. Define clear schemas at the application level, use generated columns for frequently queried paths, and prefer atomic updates for concurrent access. The combination of Rails validations and MySQL's JSON functions creates a robust system for handling variable data structures while maintaining query capabilities and data integrity where they matter most.

10 claps
← Back to Blog