Rails where clause find nested array value in hash-CodePudding

[#<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)

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("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:

https://www.postgresql.org/docs/9.5/functions-json.html