Strategies for Updating JSON Columns in Ruby on Rails

Ruby on Rails has long been my go-to framework for building scalable and reliable applications, especially when the data model needs to evolve quickly. JSON columns are a great example of that flexibility; they allow us to store deeply nested or complex structured data without constantly reshaping database schemas.

That flexibility, however, comes with trade-offs. I’ve run into several challenges while updating JSON columns in production Rails applications, especially as the data grows and the update frequency increases. In this post, I’ll walk through the most common problems I’ve personally faced while modifying JSON columns in Rails—and the practical ways I’ve learned to deal with them.

Challenges in updating JSON columns

JSON columns are convenient and flexible, but that same structure often makes updates harder than expected in Rails applications. In my experience, most issues start appearing once JSON data becomes nested and is updated frequently. When modifying JSON columns, these are the problems I see most often.

Nested Data:  JSON columns often contain deeply nested data, and updating a single nested attribute without overwriting the entire JSON object can quickly become tricky. I’ve had to be especially careful to ensure updates are applied to the correct key; one small mistake can wipe out unrelated data. Here is a sample JSON column data.

Concurrency: When multiple updates hit the same JSON column at the same time, concurrency issues become very real. I’ve seen cases where one update silently overwrote another, simply because both operations modified the full JSON document.

Validation: Validating JSON data is another area where things get complicated, especially when conditional rules or hierarchical structures are involved.

In the above example, the User’s email should be present and unique. The validation makes sure that the name of each User record is distinct from all other User entries and is not empty.

In the above example, the profile is a JSON column. Because of the nested nature of JSON data, it may be difficult to add validation to individual keys within JSON data.

If I need presence validation on a specific key inside JSON data, I usually end up writing custom validation logic. Uniqueness checks are even more manual, often requiring me to extract the value and compare it against existing JSON data across records.

Querying and Indexing: If we compared our normal relational columns, JSON columns in the database might not be as effective for querying and indexing. To increase performance, you might need to carefully optimize your queries and carefully think about creating appropriate indexes for JSON columns.

In ROR we can add indexes to our columns using migration.

In the above example, add_index or CREATE INDEX is used to create an index on a profile column for a specific key key_to_index within the JSON data, using the ->> operator to fetch the value as a string, and the using::gin option specifies that the Generalized Inverted Index (GIN) method should be used, which is capable for indexing JSON columns in PostgreSQL.

Partial update: Rails has functionality for updating specific columns. It has activerecord methods such as update_column or update_columns. But It doesn't provide us with a specific nested key attribute to update. Every time updating an entire JSON column updates the whole JSON document, which is very inefficient and makes complex database operations. Rails retrieves the entire JSON document from the database, modifies it in memory, and then stores the entire document back into the database. Also, there is no built-in support at the database level for partially updating keys in certain JSON documents, resulting in inefficient data transfers.

Strategies for Overcoming Hurdles

It can be difficult to validate JSON data, particularly when conditional validation rules and hierarchical structures are involved.

Use of ActiveRecord methods

Rails has activerecords methods like update_column & update_columns for updating specific columns without triggering callbacks or validations. It helps to update specific attributes. It will be helpful when JSON data is smaller.

But when we talk about updating specific JSON columns, we will face a similar issue that we discussed above.

Utilizing Database function

When it comes to changing JSON columns in Rails, utilizing database functions usually means modifying JSON data in the database using SQL commands or certain functions offered by the DBMS instead of depending only on Rails ActiveRecord methods. This method is especially helpful in situations when it's important to optimize efficiency or when the changes require intricate manipulations of nested JSON structures.

Let's take an example: In a Rails application, we have a user_courses table containing a JSON column named progress storing user progress related to course information. Let's consider we want to update a specific attribute within the progress column for a user_course with a given content_id. Instead of fetching the entire JSON data, modifying it in rails code, and then saving it back to the DB, we can use database functions to perform the update directly in SQL.

Here's an example of using the MYSQL JSON_SET function to update a nested attribute within the JSON column.

Here's an example of using PostgreSQL's jsonb_set function to update a nested attribute within the JSON column.

In the above examples

• UserCourses represents a table with a column named progress.
• JSON_SET(data, '{$.nested_key}', '#{new_value}') is the MYSQL function that sets the value of the specified nested key within the data JSON column to the new_value.
• JSONB_SET(data, '{nested_key}', '#{new_value}') is the PostgreSQL function that sets the value of the specified nested key within the data JSON column to the new_value.

Let's take the example of a nested attribute:

Existing progress column

Query Execution

After query execution

In the above example, we are updating the "status" and "progress_in_sec" within the specific key "_148". '$._148.status' '$._148.progress_in_sec' are representing the same. 

Better performance: Database operations are optimized for data manipulation resulting in faster updates compared to fetching and updating JSON data in a Rails application.

Less data transfer: Since updates are done directly at the database level, large JSON objects do not need to be transferred between the application and the database, reducing network overhead.

Atomicity and consistency: Database operations ensure that updates are performed atomically in a single transaction, maintaining data consistency and integrity.

This function is useful when you want to update individual attributes of a JSON column without overwriting the entire JSON document.

So, using database queries to update JSON columns in Rails applications provides a more efficient and better approach, especially for scenarios involving complex JSON data.

Conclusion:

Working with JSON columns in Ruby on Rails (ROR) can be tricky when it comes to updating them. Dealing with nested data, concurrency, and validation issues can be a headache.

To make life easier, developers have options. They can use basic Rails methods for simple updates, but for more precise changes, tapping into database functions like JSON_SET in MySQL or jsonb_set in PostgreSQL is the way to go.

This smarter approach speeds things up, cuts down on data back and forth, and ensures that updates happen smoothly and reliably. 

So, when dealing with complex JSON data in ROR apps, taking advantage of these database functions is definitely the way forward.