Rails scope where clause find nested array value in hash

Question

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

Jacob · Accepted Answer · 2021-11-02 13:51:12Z

8

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:

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

edited Nov 2, 2021 at 13:51

answered Nov 2, 2021 at 2:20

Jacob

963 bronze badges

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

Collectives™ on Stack Overflow

Rails scope where clause find nested array value in hash

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