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)

skilleo · Accepted Answer · 2015-07-21 05:18:00Z

13

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.

answered Jul 21, 2015 at 5:18

skilleo

2,4811 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related