Querying on Json data type in postgres Rails-4

Question

I am using Rails-4, have a Product model and stored specifications as JSon type

In Migration file, add add_column :products, :specifications, :json

sample product record is look like

#<Product id: 1, prod_id: 525141, cat_id: 6716, category_id: 5, updated: "2013-09-24 07:37:20", created_at: "2014-03-07 12:21:34", updated_at: "2014-03-07 12:32:36", eans: ["4016032274001"], skus: ["DK-1511-010F/WH"], account_id: 2, specifications: {"network"=>["PCI-Express 2.1 16x", "CardBus", "PCI-Express 3.0 16x", "PCI 64-bit, 66MHz", "PCI 64-bit, 33MHz", "PCI 32-bit, 66MHz", "PCI 3.0", "PCI 2.3", "PCI 2.2", "PCI-X", "PCI-Express 16x", "PCI-Express 8x", "PCI-Express 4x", "PCI-Express 2.0 16x", "PCI-Express 1x", "PCI", "PC Card", "ISA", "AGP 8x", "AGP 4x", "AGP 2x", "AGP 1x"], "rating"=>[4]}>

I want to query on product's Specification.eg: get all products that rating(inside specifications) equals to 4.

Is any gem available to implement this?

Answer 1

With the advent of jsonb in Rails 4.2 with Postgres 9.4, you can do this IF the rating field is a string and not an integer. Below is an example.

Dynamic.create(payload: {"rating"=>['4']})
Dynamic.create(payload: {"rating"=>['3']})
Dynamic.where("payload -> 'rating' ? '4'").count

This will give you the proper records. You will have to update your json field to jsonb. You can do this by following my response here.

You can find out more about how to work with jsonb in Rails 4.2 here.