rails ransack gem to search json column

Question

I have a field as json column and i have to search all the phone values inside that column, i have searched for it and i have not found any document using ransack. Is it possible to use ransaack to search json column? I have used the ransack to search other fields, so i have to use ransack or something that can combine both result

my json column looks like this

{"phone1"=>"", "relationship_type1"=>"", "relationship_name1"=>"", "phone2"=>"", "relationship_type2"=>"", "relationship_name2"=>"", "phone3"=>"", "relationship_type3"=>"", "relationship_name3"=>"", "phone4"=>"", "relationship_type4"=>"", "relationship_name4"=>""}

phone1, phone2,3,4 should be searcheable.

Lefkowitz

Answer 1

See the Ransack wiki

For each JSON column add one of these to your model, and it will enable your JSON column to be ransacked as normal columns:

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

Wesle

Answer 2

Having a table with a json column called properties, you should be able to tell ransack to look inside the phone1 with the following snippet:

ransacker :phone1 do
  Arel.sql("table.properties ->> 'phone1'")
end

Nichani

Would you like to improve your code-only answer by adding some explanation? – Floorboard

While this code may answer the question, it is better to explain how to solve the problem and provide the code for reference. Code-only answers have low utility and are often confusing. – Tyrus

Answer 3

For Rails 4.2+ (Arel 6.0+) :

• model_name : Employee
• column_name: extra_details (jsonb type)
• json_key : hobbies

# in models/employee.rb
ransacker :hobbies do |parent|
  Arel::Nodes::InfixOperation.new('->>', parent.table[:extra_details], Arel::Nodes.build_quoted('hobbies'))
end

Test it console :

Employee.search({ "hobbies_cont" => "Reading" }).result
# or
Employee.ransack({ "hobbies_cont" => "Painting" }).result

• both search & ransack work. search will be deprecated in the next major version of Ransack.

• -> does not work as intended. ->> works.

Ransack Wiki.

Blog/tutorial.

Mussel

Answer 4

The above answer is correct except that Arel 6+ will raise an exception if you send in just a string for the last arg, you have to use Arel::Nodes::build_quoted('phone1') instead of 'phone1 in this example

Also, I found that if you are going to use a ransacker on the model you have to call it with Model.search instead of Model.ransack there may be some exception to this but using Model.search is what worked for me.

#search takes a hash of the ransacker you're going to use added with the predicate you are working with (e.g. phone1_cont, or phone1_eq)

So to make the above work you'd have to call Model.search({phone1_cont: "555"})

Novgorod