BinaryWebPark

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.

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
<% 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 “examplesearchoptions” 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.