Rails + MySQL: Querying JSON

Here's how to use ActiveRecord to query a JSON column in MySQL.

In this example we have a json column user_settings. We want to find which records have theme set with a value.

Model.where("JSON_EXTRACT(user_settings, '$.theme') IS NOT NULL").count

JSON_EXTRACT is the most common MySQL function that I use. You have other options as well, they are all listed in the docs here.

Indexing JSON columns

If this is a common query you are running, you should index it. Learn how from this blog post: Index JSON in MySQL.