Querying sqlite3 JSON columns in Rails
This post is an effort to provide the next developer searching for answers with some guidance on using sqlite with the JSON1 extension. When it comes to querying JSON columns in Ruby on Rails the internet didn't seem to have any applied examples.
Here is some of my notes on searching JSON column data with ActiveRecord.
You should be running a newer version of sqlite that has JSON1 extension compiled by default. If you feel your data is not saving as JSON you'll know pretty fast due to having to parse it to JSON on the server side.
The JSON data structure for a project I'm working on looks a bit like below. This is stored in a column named details.
{
"contact":
{
"email":"user@email.com"
},
"pageDetails":
{
"userAgent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:82.0) Gecko/20100101 Firefox/82.0",
"pageUrl":"http://localhost:4000/",
"currentDateTime":"2020-11-12T07:00:04.681Z",
"ipAddress":"127.0.0.1"
}
}
To query this you can use JSON1 sqlite extensions within ActiveRecord:
Contact.where("json_extract(contacts.details, '$.contact.email') like '%user@email.com'")
I will update this page as I run into more querying requirements on JSON columns in sqlite.