The final topic I want to explore is how to use JSON columns for advanced analytical queries.

Let’s jump back into our playground, where we have our posts table with a metadata JSON column. After getting everything set up, I’ll insert a couple of posts so we have some data to work with.

Why Use JSON Columns for Analytics?

If your table contains a JSON column, you might need to analyze the data to gain higher-level insights into what’s stored. In these cases, you’ll likely need to use table-value functions that SQLite provides for working with JSON data.

Using json_each for Table-Like Queries

Let’s break down an example query that extracts and aggregates engagement data from the JSON column:

Post.connection.execute(<<~SQL)
 SELECT 
  engagements.key AS metric,
  SUM(CAST(engagements.value as INTEGER)) AS total_count,
  AVG(CAST(engagements.value as INTEGER)) AS average_count,
  COUNT(*) AS total_posts,
 FROM posts, 
  json_each(posts.metadata, '$.engagement') AS engagements
 GROUP BY engagements.key
 ORDER BY total_count DESC;
 SQL

This query:

• Extracts engagement data from the metadata JSON column.
• Uses json_each to treat JSON keys as a table, allowing us to group and count them.
• Groups by engagement_type (e.g., clicks, shares, saves).
• Computes total engagements per type.
• Calculates the average engagements per post.

Understanding json_each in SQLite

The json_each function allows us to treat part of a JSON column like a regular table. This means we can select, group, and order JSON values just as we would with standard table data.

Running the Query: Results

When we execute the query, we get useful engagement metrics:

This aggregated data can be useful for:

• User analytics → Understanding post engagement trends.
• Performance tracking → Monitoring key metrics over time.
• Reporting dashboards → Providing insights for stakeholders.

When to Use JSON Columns vs. SQL Tables

While JSON columns offer great flexibility for read-heavy data, they are not ideal for frequent updates. If you need to modify JSON data often, it’s better to use a standard SQL table with structured columns.

That wraps up our introduction to Active Record with SQLite! Up next, we’ll build a full Rails 8 application using SQLite, applying everything we’ve learned to a real-world production-grade app.