How To Query JSON Data With Rails and Postgresql

November 21, 2017

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.

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

<% content_for :custom_search do %>
  <div id="custom_search" class="container">
    <div class="pull-right">
      <%= form_for :search, url: search_path, layout: :inline do |f| %>
              <%= f.select :field, options_for_select(example_search_options), selected: example_search_options.first %>
              <%= f.text_field :value, placeholder: "Ford, Chrysler, etc." %>
              <%= f.submit "Search" %>
            <% end %>

    </div>
  </div>
<% end %>

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

[["make", "make"], ["model", "model"], ["year", "year"]]

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

class Car < ApplicationRecord
  store_accessor :data, :make, :model, :year
end

Database schema

CREATE TABLE cars (
    id integer NOT NULL,
    data json DEFAULT '{}'::json,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
);

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.

class ModelController < ApplicationController

  def search
    @results = Model.where("data ->> ? ILIKE ?", search_params[:field], '%' + search_params[:value] + '%')
    render @results.present? ? 'index' : 'no_results'
  end

private

  def search_params
    params.require(:search).permit :field, :value
  end
end

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.

# Sort based on the Hstore data:
> 2.1.1 :022 > Post.order("data->'hello' DESC")
> => #<ActiveRecord::Relation [#<Post id: 4, created_at: "2014-04-16 01:05:49", updated_at: "2014-04-16 01:05:49", data: {"hi"=>"23", "hello"=>"22"}>, #<Post id: 3, created_at: "2014-04-16 01:05:37", updated_at: "2014-04-16 01:05:37", data: {"hi"=>"13", "hello"=>"21"}>, #<Post id: 2, created_at: "2014-04-16 01:05:28", updated_at: "2014-04-16 01:05:28", data: {"hi"=>"3", "hello"=>"2"}>, #<Post id: 1, created_at: "2014-04-16 01:05:05", updated_at: "2014-04-16 01:05:05", data: {"hi"=>"2", "hello"=>"1"}>]>

> # Where inside a JSON object:
> Record.where("data ->> 'likelihood' = '0.89'")

> # Searching nested json object:
> 2.1.1 :130 > r.column_data
> => {"data1"=>[1, 2, 3], "data2"=>"data2-3", "array"=>[{"hello"=>1}, {"hi"=>2}], "nest"=>{"nest1"=>"yes"}}
> 2.1.1 :130 > Record.where("column_data -> 'nest' ->> 'nest1' = 'yes' ")

> # Searching within array:
> Record.where("column_data #>> '{data1,1}' = '2' ")

> # Searching within a value that’s an array:
> Record.where("column_data #> '{array,0}' ->> 'hello' = '1' ")

# this only find for one element of the array.

> # All elements:
> Record.where("column_data ->> 'array' LIKE '%hello%' ")

> # This is advised against in rails, use below:
> Record.where("column_data ->> 'array' LIKE ?", "%hello%")

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.