[#<User
id: 5,
name: "Dave",
email: "[email protected]",
info:
{"years_old"=>"30",
"recent_purchases"=>["car", "house", "boat"]}>]
Let's assume that I'm saving recent purchases to {info: { recent_purchases: [])
in user model
I want to make a scope to get users with only 'car'
in recent purchases. I can't change database structure so I have to figure it out
I've tried this User.where("info->>'recent_purchases' IN (?)", 'car')
but it doesn't work
This also didn't work - User.where("info->>'recent_purchases' = ?", ["car"])
Edit: Thanks to @Jacob, this worked perfectly!
User.where("where info -> 'recent_purchases' ? :car and jsonb_array_length(info -> 'recent_purchases') = 1", car: 'car')
Edit2:
If you want to create a scope for a user that has a car in his recent_purchases, that's how you do it:
scope :where_cars_included, -> { where("info::jsonb -> 'recent_purchases' ? :purchase", purchase: 'car') }
For scope with ONLY cars in recent_purchases:
scope :only_cars, -> { where("info::jsonb -> 'recent_purchases' ? :purchase and json_array_length(info -> 'recent_purchases') = 1", purchase: 'car') }
There is a small guide that can help:
How to query PostgreSQL's json fields from Rails
CodePudding user response:
Get users with a car in recent_purchases
User.where("info -> 'recent_purchases' ? :car", car: car)
Get users with only a car in recent_purchases
User.where("info -> 'recent_purchases' = to_jsonb(array[:car])", car: car)
an alternative to the previous example
User.where("info -> 'recent_purchases' ? :car and jsonb_array_length(info -> 'recent_purchases') = 1", car: car)
If info is json instead of jsonb then cast it to jsonb
info::jsonb
See PostgreSQL documentation for more ways to query against json/jsonb: