Using Ransack to search for a key in PostgreSQL JSON column

Starting with v9.2, PostgreSQL added native JSON support which enabled us to take advantage of some benefits that come with NoSQL database within a traditional relational database such as PostgreSQL. While working on a Ruby on Rails application that used PostgreSQL database to store data, we came a across an issue where we needed to implement a search by key within a JSON column. We were alredy using Ransack for building search forms within the application, so we needed a way of telling Ransack to perform a search by given key in our JSON column.

This is where Ransackers come in.

The premise behind Ransack is to provide access to Arel predicate methods.

You can find more information on Arel here.

In our case we needed to perform a search within transactions table and payload JSON column, looking for records containing a key called invoice_number. To achieve this we added the following ransacker to our Transaction model

ransacker :invoice_number do |parent|
   Arel::Nodes::InfixOperation.new('->>', parent.table[:payload], 'invoice_number')
end

Now with our search set on link_type_cont (cont being just one of Ransack available search predicates), if the user entered for example 123 in the search filed, it would generate a query like this:

SELECT  "transactions".* FROM "transactions"  WHERE ("transactions"."payload" ->> 'invoice_number' ILIKE '%123%')

basically performing a search for records in transactions table that have a key called invoice_number with value containing a string 123, within a JSON column payload.

Share Article

Follow Us

Return to Previous Page