In BlogPost, Rails, SQL

How To Query JSON Data With Rails and Postgresql

Recently, I had to build a simple search to allow users to search for text in specific fields in a JSON data store. Since the JSON data was already there, we decided to take advantage of that using Postgresql’s ILIKE operator and its native JSON search.

postgresql 3 color logo

It was built on Rails 5 and Postgresql 9.6, but should be adaptable.

I could have used ElasticSearch, but given the time constraints and actual end user requirements, it was decided this would be overkill.

Step 1: The Search Form

The search form is pretty basic. It’s a Rails form with no model backed object using Twitter Bootstrap styling.

Drop Down with Search Button Form

The “example_search_options” is an array that looks like this:

It is structured this way to cater to the Rails options_for_select helper API.

Step 2: JSON Data Attributes in Model

One thing that Rails gives you to make it easier to access your json data store is the store_accessor method. Instead of having to call something like “car.data[:make]”, you can just call “car.make” to access the value for “make” in the json data store.

Below is the model setup and the database schema.

Rails model setup
Database schema

Step 3: Search Results from Rails Controller

Below is the controller setup for the search. I’m using the ILIKE operator which is specific to Postgresql to do a case-insensitive search. The setup also minimizes the chance for SQL injection.

Other Searchable Postgres Query Examples

While looking for conventions on how to do a Postgresql search, I found this post from StackOverflow.

It’s a handy list of ways you can query your json data depending on its structure. I’m republishing it here for convenience.

Summary

If you’re wanting to power a simple search over your Postgresql data, consider using the ILIKE operator in conjunction with a json data store. It’s pretty simple to setup on a Rails application.

Recent Posts