In addition to virtual columns and enums, there’s one final higher-order column type I want to explore: JSON columns.

Jumping back into our playground, we have our posts table with title and body columns. This time, I’ve added a metadata JSON field with a default value of an empty hash. Our Post model is set up, so let’s create our posts table and examine how JSON columns work in Active Record.

Once the table is created, we see the metadata column is properly defined as JSON with an empty hash as the default value. Now, let’s create a couple of posts so we can explore JSON-specific operators and queries.

Working with JSON Data in SQLite

Active Record correctly serializes Ruby hashes into proper JSON strings before storing them in the database.

SQLite has many built-in JSON functions, with a major update in 2022, making them stable and performant. If you want to explore all the available functions, check out the SQLite JSON documentation linked.

For now, let’s focus on the core JSON operations that are most useful in Active Record.

Accessing JSON Values in Queries

To extract values from JSON columns, we use special JSON operators.

Retrieving a Top-Level JSON Key Let’s extract the timestamp key from the metadata column:

Post.select("metadata->>'timestamp'")

This query returns the timestamp strings stored inside our JSON data.

Difference Between ->> and -> Operators

SQLite provides two main operators for accessing JSON data:

• ->> (double arrow) → Returns the raw value.
• -> (single arrow) → Returns the value as a quoted JSON string.

Accessing Nested JSON Values

To retrieve values from deeply nested JSON objects, we chain multiple JSON operators:

Post.select("metadata->>'source'->>'url'")

This extracts the URL value from within a nested source key.

An alternative shorthand uses dot notation:

Post.where("metadata ->>'$.source.domain' = ?", "github.com")

This allows easier access to deep JSON fields without repeating multiple arrow operators.

Filtering Records Based on JSON Values

We can use JSON conditions inside WHERE queries.

For example, let’s find all posts where the source.domain is github.com:

Post.where("metadata ->>'$.source.domain' = ?", "github.com")

This ensures that only posts with matching JSON values are returned.

Similarly, we can filter based on numeric values stored in JSON:

Post.where("metadata ->>'$.source.word_count' = ?", 1000)

This query finds all posts where word_count is greater than 1,000.

Using JSON Columns in Aggregate Queries

Beyond filtering, JSON columns are useful for aggregations like grouping and averaging.

1. Grouping Posts by a JSON Value

To count posts by source.domain, we can group by a JSON key:

Post.group("metadata ->>'$.source.domain'").count

This groups posts by their source domain and returns a count for each domain.

2. Calculating Averages on JSON Numeric Data

We can also calculate averages from numeric JSON fields:

Post.group("metadata ->>'$.source.domain'").average("metadata"->>'$.content.word_count'")

This computes the average word count for each source domain.

Final Thoughts on JSON Columns in SQLite

Unlike some other Active Record query methods, there is no built-in Ruby method for querying JSON columns. Instead, we must use raw SQL snippets inside where statements. This is a normal part of working with JSON in Active Record, and there’s nothing wrong with using SQL snippets where needed.

In the next video, we’ll explore advanced techniques to further extend JSON functionality in Active Record and SQLite.