When developing Ruby on Rails applications, managing JSON data efficiently is a common requirement, especially when dealing with various database systems. While PostgreSQL’s JSON capabilities often receive much attention, MariaDB also offers a practical approach for handling JSON data, even though it handles JSON types differently. This post explores how to work with JSON data in Rails when using MariaDB as the database backend, focusing on the correct use of migrations, models, and data manipulation.

Understanding JSON in MariaDB with Rails

MariaDB implements the json type as an alias for the LONGTEXT type, adding a validation layer to ensure any stored data is valid JSON. This implementation differs from databases like PostgreSQL, where json and jsonb are distinct types. When you use Rails with MariaDB, defining a column as json in your migration tells Rails and MariaDB that the column will specifically store JSON data, enabling automatic validation for JSON correctness.

Migration with JSON Column

When defining a table in Rails, you can specify a column as JSON type, which is internally treated as LONGTEXT by MariaDB but with added validation:

class CreateYourTable < ActiveRecord::Migration[6.0]
  def change
    create_table :your_table_name do |t|
      t.json :your_json_column

      t.timestamps
    end
  end
end

This migration creates a your_json_column in your_table_name where the data must be valid JSON. Despite the json type here, remember that MariaDB internally treats this as a LONGTEXT type with JSON validation. The database schema.rb file will be updated with the following:

t.text "your_json_column", size: :long, collation: "utf8mb4_bin"
t.check_constraint "json_valid(`your_json_column`)", name: "your_json_column"

Model Interaction

Model Configuration for JSON Serialization

In Rails, when interacting with a json column in MariaDB, it’s good practice to explicitly declare JSON serialization in your model. This step ensures that Rails handles the conversion of Ruby objects to JSON strings and vice versa seamlessly:

class YourModel < ApplicationRecord
  serialize :your_json_column, coder: JSON
end

With this declaration, Rails automatically serializes Ruby hashes or arrays assigned to the your_json_column attribute into JSON strings for storage in the database. When retrieving the your_json_column attribute, Rails deserializes the JSON string back into the appropriate Ruby data structures.

Working with JSON Data

With the setup above, interacting with the JSON data in Rails is straightforward. You can assign Ruby hashes or arrays directly to the your_json_column attribute, and Rails takes care of the serialization:

model = Model.new

model.your_json_column = { key: 'value', array: [1, 2, 3] }

model.save

model.your_json_column['key'] => 'value'

Rails converts the assigned hash to a JSON string for storage in MariaDB. When you retrieve model.your_json_column, you get the original Ruby hash back, thanks to Rails’ deserialization process.

Indexing JSON Data in MariaDB with Rails

When it comes to optimizing the performance of JSON data queries in MariaDB, indexing plays a crucial role, especially as your data grows in size and complexity. However, because MariaDB stores JSON data as a LONGTEXT under the hood, traditional indexing methods for JSON objects, as seen in databases like PostgreSQL, are not directly applicable. Nonetheless, MariaDB offers a way to index JSON data through generated columns, which can significantly enhance query performance on JSON attributes.

Creating a Generated Column for Indexing

To index a JSON attribute in MariaDB, you can create a virtual generated column that extracts the JSON value you wish to index and then index that generated column. Here’s how you can achieve this within a Rails migration:

class AddIndexToYourJsonColumnInModels < ActiveRecord::Migration[7.1]
  def change
    # Adding a generated column to extract a JSON attribute
    add_column :models, :your_json_column_key, :string, as: 'JSON_UNQUOTE(JSON_EXTRACT(your_json_column, "$.key"))', stored: true

    # Adding an index to the generated column
    add_index :models, :your_json_column_key
  end
end

In this example, your_json_column_key is a virtual column that extracts the value associated with the key in the your_json_column JSON column. The JSON_UNQUOTE and JSON_EXTRACT functions are used to navigate the JSON object and extract the value. By setting stored: true, MariaDB stores the result of this expression physically on disk, allowing it to be indexed.

Querying Using the Indexed Attribute

When inserting or updating data in your Rails application, you only focus on the actual JSON column (your_json_column in this case). You do not need to manually update your_json_column_key because MariaDB handles this for you.

For example, when you create or update a Model instance:

model = Model.new

model.your_json_column = { key: 'value', another_key: 'other_value' }

model.save

MariaDB automatically computes the value for your_json_column_key based on the your_json_column column’s content. If you query the your_json_column_key afterward, you’ll get 'value', which is the result of the extraction expression defined in the migration.

When querying, you can leverage the your_json_column_key column to take advantage of the index. Even though you don’t manually store data in your_json_column_key, you can query it as if it were a regular column:

Model.where(your_json_column_key: 'value')

This query benefits from the index on your_json_column_key, improving performance over scanning the entire your_json_column column. MariaDB ensures that the generated column (your_json_column_key) is consistently synchronized and indexed for optimal query performance.

Conclusion

While MariaDB’s JSON implementation might not be as robust as PostgreSQL’s, Rails provides the tools necessary to work with JSON data effectively in a MariaDB-backed application. By properly setting up your migrations and models, you can enjoy seamless JSON serialization and deserialization in Rails, making your data interactions more straightforward and efficient. This approach ensures that your application code remains clean and focused, leveraging Rails’ ORM capabilities to handle the nuances of JSON data storage and retrieval.