current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

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)

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.