CODEWITHSUNDEEP
ruby-on-railsjsonpostgresqlrails-postgresqljsonb
Mathieu
Mathieu

Reputation: 4787

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

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 ?

Upvotes: 7

Views: 3114

Answers (1)

skilleo
skilleo

Reputation: 2481

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.

Upvotes: 13

Related Questions