Query an array of json with Active Record (Rails 4/postgresql9.4)

Question

I have Deal model who has an attribute called 'info' with this structure:

Inside the column 'info' on Deal:

Deal1.info = [{"deal_id":"4","text1":"qqq","text2":"sqsq","image1":"sqqs","video1":"sqsq"},{"deal_id":"5","text1":"sqqs","text2":"qq"}]

# no image here inside the json
Deal2.info = 
[{"deal_id":"4","text1":"qqq","video1":"sqsq"},{"deal_id":"5","text1":"sqqs","text2":"qq"}]

The column was defined in the mligration as json

add_column :deals, :info, :json, default: '[]'

How can I query this in jsonb with active record ?

• find all the deals where infos contain at least one deal_id = 4

• find all the deal where infos contain at lest a json block ({ }) with a key called 'image1' (it should only output Deal1 , and not deal2)

Answer 1

I had a similar column and I had to change the column type from json to jsonb.

add_column :deals, :info, :jsonb, default: [], null: false, index: true

After getting the datatype to jsonb I was able to perform this type of activerecord query.

Info.where('deals @> ?', '[{"deal_id":"4"}]')

I'm not quite sure how to write all of these yet with activerecord (http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE) to achieve your 2nd bullet point.