Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

337
Views
Rails scope where clause find nested array value in hash
[#<User
  id: 5,
  name: "Dave",
  email: "example@gmail.com",
  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

over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

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

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!